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
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