Confusion with Foreign Keys 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} -&gt; offense_id, cell_block_id, etc.


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

 {person_ssn} -&gt; 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?
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.