NickName (da****@rock.com) writes:
I ran DBCC CHECKCONSTRAINTS
with all constraints option
against several databases, at least one of them I know that has orphen
records (for I created one for testing), and yet, DBCC ... returned
"(1 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
"
across board, how come?
I don't know exactly how you define orphan rows. Then again, if you were
able to create a row that violated the foreign-key constraint, then the
constraint is obviously not enabled, and Books Online says that DBCC
CHECKCONSTRATINS only checks enabled constraints. The repro below demon-
straints.
CREATE TABLE main1 (a1 int NOT NULL,
CONSTRAINT pk_main1 PRIMARY KEY(a1))
go
CREATE TABLE main2 (a2 int NOT NULL,
CONSTRAINT pk_main2 PRIMARY KEY(a2))
go
CREATE TABLE child (a1 int NOT NULL,
a2 int NOT NULL,
CONSTRAINT pk_child PRIMARY KEY (a2, a1),
CONSTRAINT fk_child1 FOREIGN KEY(a1)
REFERENCES main1 (a1),
CONSTRAINT fk_child2 FOREIGN KEY(a2)
REFERENCES main2 (a2))
go
INSERT main1 (a1) VALUES (1)
INSERT main2 (a2) VALUES (2)
INSERT child (a1, a2) VALUES (1, 2)
go
ALTER TABLE child NOCHECK CONSTRAINT fk_child1
ALTER TABLE child NOCHECK CONSTRAINT fk_child2
go
INSERT child (a1, a2) VALUES (11, 2)
INSERT child (a1, a2) VALUES (1, 12)
go
ALTER TABLE child WITH NOCHECK CHECK CONSTRAINT fk_child1
go
DBCC CHECKCONSTRAINTS ('child')
go
DROP TABLE child, main1, main2
You can use the objectproperty() function to find disabled constraint. The
repro also shows hows to enable the constraints, but better is to say
WITH CHECK CHECK. The you will check the constraint when you enabled it,
and you will get an error if there are orphans. No report of the orphans,
though. The good thing with WITH CHECK is that the constraint will be
trusted by the optimizer and can thus be used in query plans.
--
Erland Sommarskog, SQL Server MVP,
es****@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp 
