CODEWITHSUNDEEP
phpmysql
user1926711
user1926711

Reputation: 81

Can a column in a table be both a primary key and also a foreign key?

Can somebody please tell me if it's okay to have a foreign key act as a primary key in a table?

Example is, I have a PATIENT table with PATIENT_ID as a primary key, and I'd also like to have PATIENT_ID as foreign key and at the same time a primary key to ASSESSMENT table (which contains the vital signs of the patient). Is it OK or is it going to produce problems?

Upvotes: 2

Views: 2690

Answers (5)

Xnoise
Xnoise

Reputation: 522

The prerequisite for a foreign key is that the column being a foreign key needs to be a key in both the tables. So from this point of view, you are perfectly safe. The only problem that can get raised is for future developments in the application, when you might need to have a one to many relation between the 2 tables, in which case it is pretty bad to use this approach. It will not be very hard to change this behavior later on on the database level, but it might prove to be quite tricky at the application level.

Honestly i pretty much doubt you need to actually use a foreign key between the 2 tables, since the ids will always be the same, and this kind of defeats the purpose of a foreign key. Also, most primary keys are usually auto incremented which is not the case for you, and this could lead to other problems in future development.

Upvotes: 0

Salman Arshad
Salman Arshad

Reputation: 272036

This is called a one-to-one relation.

Is it ok or is it going to produce problems?

It is OK as long as you have a good reason for keeping the data into two tables.

Upvotes: 1

hakre
hakre

Reputation: 197624

Is it ok or is it going to produce problems?

I'd say it is okay. A little problem this produces is maybe that it made you need to ask the question. So if you actually want to learn more you should ask yourself about why specific you are unsure so that you can actually learn something.

Upvotes: 1

Gagan
Gagan

Reputation: 573

yes foreign key can work as a primary key in a table.

Upvotes: 1

Akhil
Akhil

Reputation: 2602

That is OK as long as you can maintain a 1 to 1 relation . There is no restriction from database side

Upvotes: 4

Related Questions