Darren
3/15/2007 7:42:00 PM
Where does SQL Server store the metadata for the link between a PK or Unique
contraint and its corresponding index that is automagically created? They
are obviously linked as when you drop the constraint it also drops the index.
This behaviour is of course echoed in BOL.
The reason I am asking is that we have noticed an odd behaviour from DDL
forward engineered from CA's ErWin (see script below). Under certain
conditions, ErWin will script the PK constraint and then alter the table
adding a unique clustered index on the PK column(s). This script is
disrupting other metadata queries in several observed fashions. The primary
issue is that this apparently keeps you from enabling the "Not for
replication" option on the PK constraint, or more specifically, after you
enable it, it does not remain enable when scripted from QA, EM, or by a
snapshot publication.
If anyone is aware of where SQL Server maintains this metadata link between
th constraint and its index, this would be useful to verify that the link is
missing when the constraint and index are cobbled together in the fashion
seen below.
Thanks.
--Begin Script
CREATE TABLE [dbo].[drvg_dist_xref]
(
[drvg_dist_id] [int] IDENTITY(1, 1) NOT FOR REPLICATION NOT NULL
,[lng_desc] [varchar](3000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,[rowguid] uniqueidentifier ROWGUIDCOL NOT NULL
)
ON [PRIMARY]
GO
ALTER TABLE [dbo].[drvg_dist_xref]
ADD CONSTRAINT [DF__drvg_dist__rowgu__1F46DA62] DEFAULT ( newid() ) FOR
[rowguid]
,CONSTRAINT [PK__drvg_dist_xref__0C7E1EB1] PRIMARY KEY NONCLUSTERED (
[drvg_dist_id] ) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE UNIQUE CLUSTERED INDEX [XPKdrvg_dist_xref] ON [dbo].[drvg_dist_xref]
( [drvg_dist_id] ) WITH FILLFACTOR = 90 ON [PRIMARY]
--End Script
--
....