CODEWITHSUNDEEP
sqlsql-serverforeign-keys
Adam Goss
Adam Goss

Reputation: 1027

SQL: Foreign Key on Column Pointing to Same Column

I've recently come across a bizzare scenario in one of our legacy databases and our DBA (not the one that created it) is uncertain why this would have been done and what benefit it would have. The only thing we can think of is that it was done in error. The following foreign key constraint has been defined on a table:

CREATE TABLE [dbo].[SomeTable]
(
    [Id] SMALLINT IDENTITY (1,1) NOT NULL,
    -- other columns

    CONSTRAINT [PK_SomeTable] PRIMARY KEY CLUSTERED ([Id] ASC)
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[SomeTable] WITH CHECK ADD CONSTRAINT [FK_SomeTable_SomeTable]
FOREIGN KEY ([Id]) REFERENCES [dbo].[SomeTable] ([Id])
GO

ALTER TABLE [dbo].[SomeTable] CHECK CONSTRAINT [FK_SomeTable_SomeTable]
GO

Anyone know or have any thoughts on what this may actually do?

Upvotes: 4

Views: 910

Answers (2)

Henrik S
Henrik S

Reputation: 16

The rows cannot be deleted because of the foreign key constraint. At least SQL Server isn't smart enough to realize that the target of the foreign key is actually the same row. The same goes for trying to edit the value of the primary key.

I guess this could be by design in order to prevent the rows from ever be changed or deleted, but a better solution would be to use triggers instead.

Upvotes: 0

Katy
Katy

Reputation: 296

It's possible to use a foreign key that references to the same table for implementing hierarchy, but of course we should use different columns in the same table. I think the creator of this table just made mistake.

Upvotes: 1

Related Questions