Can a Foreign Key Refer to a Primary Key in the Same Table

Can a foreign key refer to a primary key in the same table?

I think the question is a bit confusing.

If you mean "can foreign key 'refer' to a primary key in the same table?", the answer is a firm yes as some replied. For example, in an employee table, a row for an employee may have a column for storing manager's employee number where the manager is also an employee and hence will have a row in the table like a row of any other employee.

If you mean "can column(or set of columns) be a primary key as well as a foreign key in the same table?", the answer, in my view, is a no; it seems meaningless. However, the following definition succeeds in SQL Server!

create table t1(c1 int not null primary key foreign key references t1(c1))

But I think it is meaningless to have such a constraint unless somebody comes up with a practical example.

AmanS, in your example d_id in no circumstance can be a primary key in Employee table. A table can have only one primary key. I hope this clears your doubt. d_id is/can be a primary key only in department table.

Foreign Key referencing Primary key in the same table

I know of no reason why this, specifically, could provide a benefit - so I'm going to go with option 2 - a mistake.

Of course, if it was different columns in the same table, that would make sense, and as @Jignesh.Raj points out, that would form some kind of hierarchy.

You can even sometimes end up with multiple hierarchies within the same table with such multi-column references:

CREATE TABLE T (
   GroupID int not null,
   ItemID int not null,
   ParentItemID int null,
   constraint PK_T PRIMARY KEY (GroupID,ItemID),
   constraint FK_T_Parent FOREIGN KEY (GroupID,ParentItemID) references T (GroupID,ItemID)
)

In the above, the GroupID column always references itself.

But as I say, with your current table, where both columns only reference themselves, it makes no sense.

MYSQL foreign key to the same table?

Yes, a foreign key can reference the same table.

A classic example of this, often included in the sample database that comes with the database, is:

create table employee (
    id int not null primary key,
    manager_id int not null references employee,
    ...
)

Such references are known as self-referencing foreign keys. If defined like the above,they automatically prevent deletion of a "parent" record if there are "children" that reference it.

Foreign Key Refers the Primary Key Columns of Same Table

I would approach this as follows:

SELECT
    co1.OfficeId,
    o1.Name,
    o2.Name AS ParentOffice
FROM Offices o1
LEFT JOIN CivilOffices co1
    ON o1.OfficeId = co1.OfficeId
LEFT JOIN Offices o2
    ON co1.ParentOffice = o2.OfficeId;

We do a self-join on the CivilOffices table to match each record with a potential parent. Then, we use two separate joins to Offices to bring in the office name as well as possibly the parent office name, should the latter exist.

reference a foreign key to a primary key within the same table

For MS Access, you need to use CONSTRAINT keyword like:

CREATE TABLE Employees ( 
   P_Id INTEGER NOT NULL,
   Super_Id INTEGER NOT NULL,

   PRIMARY KEY(P_Id),
   CONSTRAINT FK_SuperId FOREIGN KEY (Super_Id)
   REFERENCES Employees(P_Id)
);

Can I have a Table with primary Key which is a Foreign Key in more than one Table

It is not fully clear to me what you want to achieve with the group. But to answer your question: it is possible to reference the same table as foreign key in different tables. So yes, you can use groupId as foreign key in Role_Table and Admin_Table.

However there are some potential issues here: Admin_Table has its own roleId. This is either an undesired redundancy, if your code has to keep both consistent, or a design that is potentially ambiguous, since a given adminId could be associated directly with one groupId, and indirectly via the role table to another groupId. Thera are some situations where this triangular relationship is needed on purpose, but are you sure in your case?

There is a potentially bigger issue: In Group_Table, a groupId (primary key) is associated to a roleId (foreign key). This means that there is only one role possible for a given group. At the same time, in the Role_Table a roleId (primary key) is associated to a groupId (foreign key). This means that either there is a one-to-one relationship between groups and roles (that needs to be kept in sync), or roles and groups form a complex interleaved hierarchy.

Does a foreign key always refer to a primary key?

"What if there's two foreign key on the same table that refer to the same primary key?
"

Any number of child tables can reference a parent table. There are certain situations in which it is possible for a child table to have more than one foreign key on the same parent.

For instance, any form of sporting contest has opponents of the same type - player, team, etc. So a match will have two instances of that entity, hance the child table will have two columns with foreign keys referencing the same primary key.

create table player (
    player_id number not null primary key
    , name varchar2(30) not null unique
);
create table match (
    match_id number not null primary key
    , player_1 number not null 
    , player_2 number not null 
    , match_played date not null
    , result varchar2(10)
    , constraint match_player1_fk foreign key (player_1) references player
    , constraint match_player2_fk foreign key (player_2) references player
);

A foreign key can reference a unique constraint rather than a primary key. However this is not standard practice. It is the convention to use unique keys to enforce candidate keys - business keys - and these are not always suitable for use as foreign keys.

For instance in my example, PLAYER.NAME is a unique key: every player must have a distinct name. However, it would not be appropriate to use NAME as the foreign key on MATCH because people can change their name. It is more convenient to use the synthetic primary key, PLAYER_ID because that will not change over the lifetime of the PLAYER record.

Is it fine to have foreign key as primary key?

Foreign keys are almost always "Allow Duplicates," which would make them unsuitable as Primary Keys.

Instead, find a field that uniquely identifies each record in the table, or add a new field (either an auto-incrementing integer or a GUID) to act as the primary key.

The only exception to this are tables with a one-to-one relationship, where the foreign key and primary key of the linked table are one and the same.

---

Related Topics