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?
