CODEWITHSUNDEEP
sql-serversql-server-2008-r2
James World
James World

Reputation: 29796

FK referencing PK with same column

I was surprised to discover I can create a foreign key that refers to a primary key defined on the exact same table and column, for example:

ALTER TABLE dbo.Foo WITH CHECK ADD CONSTRAINT FK_Foo_Foo FOREIGN KEY(FooId)
REFERENCES dbo.Foo (FooId)

I have a customer DB with such an FK defined and it seems completely redundant.

Does it serve any purpose?

NB. This is not the situation of a hierarchical relationship - the FK field is not a different field in the same table acting as a parent ID, but rather the PK field itself.

Upvotes: 1

Views: 168

Answers (1)

SQLDiver
SQLDiver

Reputation: 2018

A foreign key which references itself is a circular reference, or infinitely recursive relationship.

The constraint would never be violated as the row is always matching itself.

My guess is that this is either an error in the design or a temporary marker for future development, which was never followed through.

This is a completely redundant feature which could either be removed or just left alone. One thing I would be conscious about is that Microsoft may ban self-referencing FKs in a later version of SQL Server. For this reason, I would be inclined to delete the constraint if future compatibility were a consideration.

For a parent-child hierarchy to work, the foreign key would have to reference a different column.

Upvotes: 1

Related Questions