Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I've been tasked to build a somewhat basic Prisoner database and I've run into a situation where I don't know what the appropriate route to take is.

I have a Prisoner table, that looks like the following:

  Primary Key                 attributes
 {prisoner_id, person_ssn} -> offense_id, cell_block_id, etc.

I have a Person table and the Primary key is {person_ssn}

 {person_ssn} -> first_name, last_name, dob, etc.

Now I am a bit confused on whether person_ssn in the Prisoner table needs to be a foreign key or not, and why. Because it's a part of the primary key, does that rule it out of being a foreign key?

share|improve this question
2  
There's no reason in principle why primary key attributes shouldn't be foreign keys. –  sqlvogel Mar 21 at 20:44
    
I agree with @sqlvogel. Looks right here, a Person can be a Prisoner many times. –  ypercube Mar 21 at 20:48
    
In my particular situation, is it necessary to have person_ssn be a foreign key? If so, how would I make it a part of the primary key as well as a foreign key in mySQL? –  Jillian Mar 21 at 20:48
    
I would say yes, because prisoner can't exist if person doesn't exist, so you have to set person_ssn as foreign key to force that. –  CodeBird Mar 26 at 20:55

2 Answers 2

You should separate the concepts. The combination {prisoner_id, person_ssn} is perfectly valid for Primary Key in the Prisoner Table. But you do not need to do that, if prisoner_id is unique. In that case, adding person_ssn to the PK is not optimal.

Adding a foreign key constraint on person_ssn checks that no person_ssn is added to Prisoner table if there is not a corresponding entry (for the same person_ssn) in the Person table. So, you should definitely add person_ssn as a foreign key in Prisoner table.

If you want fast look_ups on Prisoner table according to person_ssn, you should have an index at person_ssn column at Prisoner table. Luckily as the SO question/answers Does MySQL index foreign key columns automatically? suggest, InnoDB already adds indexes to foreign key columns (from your comments I assumed you are using MySQL.)

So:

  • Primary keys -> unique identifiers of each table row
  • Foreign key -> referential integrity with referenced table
  • Index -> Fast look ups

Having said that and as comments suggest, the same column can be used as a Foreign key and participate in a Primary Key as well. Those two concepts are independent from each other.

share|improve this answer

EDIT

By the way your tables are setup, you would have to use ssn as a foreign key but I think this is a bad idea.

I would not use the ssn as your primary key instead I would have and incremental primary key and store ssn in another column. You can still look up prisoners by ssn regardless.

I think you should redesign your person table to also not use ssn as a key as well for security purposes. You will run into multiple issues down the line. For example, what will you do if you have a person without a ssn?

Summary: recreate both tables to use auto incrementing primary keys and remove ssn from prisoner table completely.

share|improve this answer
    
This may be useful but it doesn't answer the question. –  ypercube Mar 26 at 17:31
    
The answer is that it does not need to be a foreign key and can just be used as a lookup. –  B Woods Mar 26 at 18:04
    
Please, whatever you feel should be in the answer, edit the answer and add it. Don't leave it in comments as they may be deleted, not everyone reads them and are not seen by search engines. –  ypercube Mar 26 at 19:49

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.