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?
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 a Parent_Id column that refers back to the Parent table. This relationship is one of the cornerstones of the relational 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 a Foreign Key or even multiple Foreign Keys but they don't have to be. The Parent table must related by one of the Candidate Keys but that does not have to be the Primary Key.
I suspect this is a duplicate but I cannot find it
Typically another table and even some definitions state another table
Repeating values is the norm for a FK
FK point to the PK of another (or the same table) is the common configuration. In MSSQL it can be a column(s) with a unique constraint (and not PK). The row must be uniquely identified.
Here is an example of a single table
Assume each employee has exactly one boss
The person at the top works for them-self
BossID would be a FK in the same table
Yes BossID would repeat if more than one person worked for that boss
EmpID (PK)
BossID (FK EmpID)
An invoice table with multiple reference to a the same customer
InvoiceID (PK)
CustomerID (FK Customer.ID)
You are asking three questions:
Can a foreign key be a primary key in the same table?
The answer is yes.
Here is an example. Suppose that you have a table Projects, with columns ProjectId, Name, Description, Manager, Budget, and also a table SpecialProjects, that contains additional information about certain special projects, with other columns, referenced by other tables, etc. So you have in this table a foreign key for the Project table, which contains the “general” informations about those special project. This foreign key, let’s call it FkProjectId, is also a primary key for the SpecialProjects table.
Can a foreign key be a primary key in another table?
A foreign key in a table B that refers to a table A is a column such that all its values must be present also in the primary key column of A. So, the question is meaningless if you intend that a foreign key is two different column (this of course is impossible), while the answer to this question is always “yes” if you intend that the values of the foreign keys are also values in a primary key in another table.
Can a foreign key take values that repeat themselves?
The answer is “yes”.
Consider the above example: in the table Projects the column Manager is a foreign key for an Employee table. If an employee can manage more than one project, then different rows in the Project table have the same value in the column Manager.
This should clarify things for you:- https://msdn.microsoft.com/en-gb/library/ms179610.aspx
