Yes, if it just points to itself, it already knows that it satisfies it. It is respecting the constraint. There is nothing to check.
I see dumb stuff like this all the time. Was at a site recently where they had triple-created all the FK constraints. No big surprise that it doesn’t actually check the value 3 times (to be sure, to be sure, to be sure), regardless of what it shows in the plan. It’s also one of the reasons why I always name constraints. Then you can’t accidentally multiple-create them. (Reason #3723924 for not using visual table designers). 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: ozdotnet-boun...@ozdotnet.com [mailto:ozdotnet-boun...@ozdotnet.com] On Behalf Of David Burstin Sent: Tuesday, 9 February 2016 4:06 PM To: ozDotNet <ozdotnet@ozdotnet.com> Subject: Re: SQL foreign key question On 9 February 2016 at 16:02, Greg Low (罗格雷格博士) <g...@greglow.com<mailto:g...@greglow.com>> wrote: 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. For me the bigger question is how this actually works. Does the sql engine just decide to ignore the constraint because it is so obviously dumb? If so, am I the only one bothered by that? I would have actually preferred if no records could be added to the table, because at least then I know that the engine is respecting my constraints. 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<tel:%2B61%20419201410> mobile│ +61 3 8676 4913<tel:%2B61%203%208676%204913> fax SQL Down Under | Web: www.sqldownunder.com<http://www.sqldownunder.com/> From: ozdotnet-boun...@ozdotnet.com<mailto:ozdotnet-boun...@ozdotnet.com> [mailto:ozdotnet-boun...@ozdotnet.com<mailto:ozdotnet-boun...@ozdotnet.com>] On Behalf Of Tony McGee Sent: Tuesday, 9 February 2016 3:59 PM To: ozDotNet <ozdotnet@ozdotnet.com<mailto:ozdotnet@ozdotnet.com>> 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" <david.burs...@gmail.com<mailto:david.burs...@gmail.com>> 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?
