I have have looked for an answer for my questions everywhere but they seem confusing to me.Can a foreign key be a primary key in the same table? Can a foreign key be a primary key in another table?
Can a foreign key take values that repeat themselves?
Best Answer
Let's start with definitions then go into differences.
Primary Key There can be only one Primary Key per table. It is one of the candidate keys. A candidate key is a key comprised of a column or group of columns that uniquely identify the row. In SQL Server none of the values can be NULL but I can't say for certain that is true in other DBMSs.
Foreign Key A foreign key is a column or group of columns that
relate
two tables together. Typically this is called a parent/child relationship. So for example a child table will have aParent_Id
column that refers back to theParent
table. This relationship is one of the cornerstones of therelational
part of the RDBMS (Relational DataBase Management System). There can be multiple foreign keys on a table and in fact a foreign key can even relate a table back to itself.Comparison As you can see they really have nothing to do with each other. The column(s) that make up the
Primary Key
can be the same column(s) that are used in aForeign Key
or even multipleForeign Keys
but they don't have to be. TheParent
table must related by one of theCandidate Keys
but that does not have to be thePrimary Key
.