CODEWITHSUNDEEP
sqlsql-server-2008foreign-keys
Paul Anderson
Paul Anderson

Reputation: 1180

Foreign Key referencing Primary key in the same table

I have a table with two columns as the primary key. These two columns are also a foreign key that references the same table: (This table was created some time ago by someone who has since left the company)

CREATE TABLE [dbo].[tblItemLink](
    [ItemListID] [int] NOT NULL,
    [ItemID] [int] NOT NULL,
 CONSTRAINT [PK_tblItemList] PRIMARY KEY CLUSTERED 
(
    [ItemListID] ASC,
    [ItemID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[tblItemLink]  WITH CHECK ADD  CONSTRAINT [FK_tblItemLink_tblItemLink] FOREIGN KEY([ItemListID], [ItemID])
REFERENCES [dbo].[tblItemLink] ([ItemListID], [ItemID])
GO

ALTER TABLE [dbo].[tblItemLink] CHECK CONSTRAINT [FK_tblItemLink_tblItemLink]
GO

In practice, ItemID refers to tblItem.ItemID, and ItemListID is found nowhere else in the DB but there is a corresponding enum in the application.

Is there any reason for the primary key to also be a foreign key referencing itself (ie. some undocumented performance improvement), or is it just a mistake?

Upvotes: 5

Views: 24667

Answers (2)

Jignesh.Raj
Jignesh.Raj

Reputation: 5987

Thats how you would create a hierarchy, and could also ensure you can't have a child with an invalid parent.

See also Should you make a self-referencing table column a foreign key?

Upvotes: 4

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239636

I know of no reason why this, specifically, could provide a benefit - so I'm going to go with option 2 - a mistake.

Of course, if it was different columns in the same table, that would make sense, and as @Jignesh.Raj points out, that would form some kind of hierarchy.

You can even sometimes end up with multiple hierarchies within the same table with such multi-column references:

CREATE TABLE T (
   GroupID int not null,
   ItemID int not null,
   ParentItemID int null,
   constraint PK_T PRIMARY KEY (GroupID,ItemID),
   constraint FK_T_Parent FOREIGN KEY (GroupID,ParentItemID) references T (GroupID,ItemID)
)

In the above, the GroupID column always references itself.

But as I say, with your current table, where both columns only reference themselves, it makes no sense.

Upvotes: 6

Related Questions