Search code examples
sql-server-2008foreign-keys

What is the meaning of self referencing foreign key?

I went over a legacy database and found a couple of foreign keys that reference a column to itself. The referenced column is the primary key column.

ALTER TABLE [SchemaName].[TableName]  WITH CHECK ADD  
CONSTRAINT [FK_TableName_TableName] FOREIGN KEY([Id])
REFERENCES [SchemaName].[TableName] ([Id])

What is the meaning of it?

Solution 
  • ALTER TABLE [SchemaName].[TableName]  WITH CHECK ADD  
CONSTRAINT [FK_TableName_TableName] FOREIGN KEY([Id])
REFERENCES [SchemaName].[TableName] ([Id])

    This foreign key is completely redundant and pointless. Just delete it. It can never be violated as a row matches itself, validating the constraint.

    In a hierarchical table, the relationship would be between two different columns (e.g. Id and ParentId).

    As for why it may have been created? Quite likely through the use of the visual designer. If you right click the "Keys" node in object explorer and choose "New Foreign Key", then close the dialogue box without deleting the created foreign key, and then make some other changes in the opened table designer and save, it will create this sort of redundant constraint.