0

The books are academic and do not specify what to do with the foreign keys. For the college assignment I am building an SQL database for a dental clinic.

If I had to create foreign keys, do I need one per table or can a table specialist dental referral be left with no foreign key.

I have 5 tables in dental clinic database. appointment PK (primary key) app_id patient PK pat_id payment PK pay_id treatment PK treat_id referral PK ref_id

-- Constraints for table `appointment`
--
ALTER TABLE `appointment`
  ADD CONSTRAINT `patient` FOREIGN KEY (`pat_id`) REFERENCES `patient` (`pat_id`),
  ADD CONSTRAINT `pay_id` FOREIGN KEY (`pay_id`) REFERENCES `payment` (`pay_id`);

--
-- Constraints for table `patient`
--
ALTER TABLE `patient`
  ADD CONSTRAINT `appointment` FOREIGN KEY (`app_id`) REFERENCES `appointment` (`app_id`),
  ADD CONSTRAINT `ref` FOREIGN KEY (`ref_id`) REFERENCES `referral` (`ref_id`),
  ADD CONSTRAINT `treat` FOREIGN KEY (`treat_id`) REFERENCES `treatment` (`treat_id`);

--
-- Constraints for table `payment`
--
ALTER TABLE `payment`
  ADD CONSTRAINT `app` FOREIGN KEY (`app_id`) REFERENCES `appointment` (`app_id`),
  ADD CONSTRAINT `pat` FOREIGN KEY (`pat_id`) REFERENCES `patient` (`pat_id`);
COMMIT;
SpagettiCode
  • 31
  • 4
  • 1
    Your question is utterly confusing... I recommend [edit]ing it, include example DDL and point out what exactly isn't working or what you think is wrong with it. – sticky bit Apr 11 '21 at 08:57
  • I modified the Question. When a constraint is made I need to remove it first before deleting a foreign key and I have 3 foreign keys in table `Patient` & table `appointment` but cannot make anymore FK in the other tables due to integrity constraint. I have zeroe understanding of what a foreign key should do or the purpose of it. I should be honest. – SpagettiCode Apr 11 '21 at 10:03

1 Answers1

2

Foreign keys are used to make relationship between tables and ensure the integrity of the data in that field. A table may have zero or more foreign keys. The process of creating multiple tables and adding foreign keys and all is called Normalisation. It would be nice for you to read on it and understand it properly. This will help in your whole life.

In your case, the way I see it, assuming a patient can exist independently, the patient do not have any dependencies.

A patient has one or more appointments. However an appointment cannot exist without a patient. An appointment has only one patient, so in this case, there must be a foreign key of Patient in the table Appointment.

The appointment can have one or many payments (depending on your business case). A payment cannot exist without an appointment. However linking payment with patient is kinda redundant given that you can find the patient in the appointment associated with the payment. Still this is a case to case basis based on the business requirement.

Now let's say you have a patient with patient id 1. You are adding a new appointment but and by mistake, instead of using 1 for the foreign key patient id, another value has been used, like 100. And in the system, there is no patient with id 100. When doing the insert, you will get a foreign key constraint error, informing you no patient exists with the id 100. The foreign key ensures data integrity for the foreign keys used in the table, like the foreign key patient id in the table appointment.

atish.s
  • 1,534
  • 11
  • 19
  • I picked up on that when I was building the two tables. The patient table & the appointment table. I would like to know how to make the appointment table take up the data that I entered into the patient table. This is an excellent explanation on an FK. It clarified what I lacked in understanding. – SpagettiCode Apr 11 '21 at 19:06
  • 1
    you must write sql queries to retrieve the data. To select all the data for a patient given a specific appointment, the query will be some like 'select * from appointment a inner join patient p on a.pat_id = p.pat_id where a.app_id = X'. You must replace X by the desired appointment id whose data you want to fetch. – atish.s Apr 12 '21 at 02:05