You often see tables referring to themselves (due to some sort of hierarchical data) but having a column refer to itself seems at best pointless, at worst silly.
And reason #3723923 why I won’t ever use such a visual table designer. Regards, Greg Dr Greg Low 1300SQLSQL (1300 775 775) office | +61 419201410 mobile│ +61 3 8676 4913 fax SQL Down Under | Web: www.sqldownunder.com<http://www.sqldownunder.com/> From: [email protected] [mailto:[email protected]] On Behalf Of Tony McGee Sent: Tuesday, 9 February 2016 3:59 PM To: ozDotNet <[email protected]> Subject: Re: SQL foreign key question This can sometimes happen to a primary key if you use the visual query designer to create a FK relationship and don't change the defaults before clicking OK. It looks like it's ignored in the insert query execution plan. On 9 Feb 2016 2:32 pm, "David Burstin" <[email protected]<mailto:[email protected]>> wrote: I came across this (snipped to protect the innocent): CREATE TABLE [dbo].[V2_BREC_NMIStatusHistory] ( [NMIStatusHistoryId] INT IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL, <snip/> CONSTRAINT [PK_V2_BREC_NMIStatusHistory] PRIMARY KEY CLUSTERED ([NMIStatusHistoryId] ASC), CONSTRAINT [FK_V2_BREC_NMIStatusHistory_V2_BREC_NMIStatusHistory] FOREIGN KEY ([NMIStatusHistoryId]) REFERENCES [dbo].[V2_BREC_NMIStatusHistory] ([NMIStatusHistoryId]) ); Notice that the primary key identity field has a foreign key constraint on itself. How does this work? I would have thought that any attempt to add a record would check the table for the existence of the new key, and as it obviously wouldn’t exist yet, that would break the foreign key constraint resulting in the record not being written. But, the table has plenty of data. Anyone have any ideas how this actually works, or does it just do nothing?
