28

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.

2
  • 6
    Probably through cack handed use of the designer is my theory Oct 28, 2014 at 18:06
  • I found one an index like this in a database I'm maintaining but a very good programmer wrote. I was shocked by 2 things. 1. that i found something like this and 2. that it was so easy to find other people questioning the same thing. Mar 30, 2020 at 14:28

3 Answers 3

37

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);
1
  • 1
    Placing a constraint on the ManagerId relating to the EmployeeId in this case could be beneficial if the Manger were to be "separated". It would not allow the delete of the row if set that way. Not saying I would do it that way but it could have some benefit if your application relied on employees having managers.
    – zgr024
    Oct 28, 2014 at 18:47
7

I just found such a foreign key in my own db and it must have been myself creating it. I think this just happened by accident. If I click on "New Foreign Key" in the context menu of a table with primary key (within Management Studio, SQL 2014 Express) this already automatically creates such a foreign key referring to itself. See below:

enter image description here

If I then don't realize that I should alter that one instead of adding a new one, it will stay there. Or, if I simply click on the [Close] button meaning this would be like a [Cancel], the foreign key would still be created after saving the table definition.

So, to me such a Foreign Key makes no sense and can be removed.

1
  • It's possible that you wanted to create a FK and in the process you realize the other table hasn't set its PK yet so you cancel, go to the other table and then for some reason you forget to continue with the FK process. There you have your recursive FK.
    – Andrew
    Jul 12, 2017 at 14:47
4

Perhaps the designer wanted to disable the use of TRUNCATE TABLE?

TRUNCATE TABLE cannot be used on a table with a foreign key constraint to another table though it can be used if there are self-referential foreign keys. From the documentation for TRUNCATE TABLE (Transact-SQL):

BOL Extract

A DELETE statement without a WHERE clause has a similar effect to a TRUNCATE TABLE (removing all rows in the table) but the DELETE statement fires delete triggers, which might be a reason to allow DELETE but not TRUNCATE TABLE.

I would do this using permissions (DELETE requires delete permission, TRUNCATE TABLE requires alter table permission), but maybe there is some reason the designer couldn't do this?

Note: Even though what the designer has done does not actually disable the use of TRUNCATE TABLE, I'm still speculating that this was their intent.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.