Why would a table use its primary key as a foreign key to itself
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);
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:
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.
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):
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.