Thanks Tony. One more reason why I don't use the visual designer (now get off my lawn).
I don't like the idea that it is ignored arbitrarily. Does anything else get arbitrarily ignored? On 9 February 2016 at 15:59, Tony McGee <[email protected]> wrote: > 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]> 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? >> >
