DBCC CHECKCONSTRAINS: Verify Foreign Key Integrity

1 view

Skip to first unread message

ragav...@gmail.com

unread,

Aug 28, 2006, 5:46:33 AM8/28/06

to SQL Developers

*******************************************************************
DBCC CHECKCONSTRAINS: Verify Foreign Key Integrity
*******************************************************************

There are two scenarios in SQL Server that can leave tables with rows
that are not bound by an existing FOREIGN KEY constraint or CHECK
constraint:

1. Newly added constraints ignore existing table data if created using
"WITH NOCHECK".

2. Re-enabled constraints ignore all table updates and inserts that
took place since the constraint was disabled (disabled constraints
appear with a red icon in Teratrax Database Manager. Right-click on
constraint name to enable it).
Use DBCC CHECKCONSTRAINS to check the integrity of your FOREIGN KEY
constraints and CHECK constraints and make sure there are no constraint
violations. The command returns a list of rows containing all
violations. DBCC CHECKCONSTRAINTS does not return any rows if there are
no constraint violations.

Syntax

To check all constraints in the current database:

DBCC CHECKCONSTRAINTS

To check all constraints on a single table:

DBCC CHECKCONSTRAINTS ('table1')

You can run this sql command from the SQL window in Teratrax Database
Manager. By default, DBCC CHECKCONSTRAINTS returns the first 200 rows
only.
DBCC CHECKCONSTRAINTS checks against enabled constraints only unless
WITH ALL_CONSTRAINTS is specified. The returned result consists of
table name, constraint name and a WHERE clause that identifies the
violating row in the table.