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? > >
