Why Use a Primary Key as a Foreign Key to Itself in SQL Server

foreign keyprimary-keysql server

Looking through a database, I came across a table that used its primary key as a foreign key to itself.

I've seen that a table can have a foreign key to itself to build a hierarchy structure, but it would use another column to reference the primary key.

Since the primary key is unique, in this situation wouldn't the row only be able to point back to itself? That seems to be a tautological link, since if I already have the row, then I already have the row.

Is there any reason this would be done?

Table joined to itself

I am certain the constraint is written that way (not just looking at the diagram) because the same table and column are used for both halves of the definition.

Best Answer

Like you said. A FOREIGN KEY constraint referencing the same table is typically for a hierarchy structure and it would use another column to reference the primary key. A good example is a table of employees:

EmployeeId    Int     Primary Key
EmployeeName  String
ManagerId     Int     Foreign key going back to the EmployeeId

So in this case there is a foreign key from the table back to itself. All managers are also employees so the ManagerId is actually the EmployeeId of the manager.

Now on the other hand if you mean someone used the EmployeeId as the foreign key back to the Employee table then it was probably a mistake. I did run a test and it's possible but it wouldn't have any real use.

CREATE TABLE Employee (EmployeeId Int PRIMARY KEY,
                        EmployeeName varchar(50),
                        ManagerId Int);


ALTER TABLE Employee ADD CONSTRAINT fk_employee 
    FOREIGN KEY (EmployeeId) REFERENCES Employee(EmployeeId);
Related Question