Thanks Greg - that clears it up for me. I hadn't thought about the pre-processor. Cheers.
On 9 February 2016 at 16:11, Greg Low (罗格雷格博士) <g...@greglow.com> wrote: > 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 > > > > *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> 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 mobile│ +61 3 8676 4913 > fax > > SQL Down Under | Web: www.sqldownunder.com > > > > *From:* 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> > *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> 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? > > >