Al Priest’s Blog

We’ve got a moderately large database here – 80gb, 5 tables and 500m rows – and we had problems updating it. Following a bout of Horizontal Partitioning we’ve now got 130 tables and those rows distributed evenly across them. However when a check constraint was updated today SQL Server 2000 wouldn’t allow data to import and we got the error message:

UNION ALL view ‘Classifier_z’ is not updatable because a partitioning column was not found.

In the back of my mind i’d remembered about trusted and untrusted check constraints and after a bit of Googling (I did use Google so I can use it as a verb ) I found this page which displays all the currently untrusted constraints in your table, and further allows you to set them to trusted. Here’s the code too, just in case that target page disappears.

To display untrusted constraints:

SELECT ctu.TABLE_SCHEMA, ctu.TABLE_NAME, cc.CONSTRAINT_NAME, cc.CHECK_CLAUSE
FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS AS cc
JOIN INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE AS ctu
ON ctu.CONSTRAINT_NAME = cc.CONSTRAINT_NAME
WHERE objectproperty( object_id(quotename(cc.CONSTRAINT_NAME)), 'CnstIsNotTrusted' ) = 1

To make all constraints trusted:

DECLARE @tablename nvarchar(300), @constraint nvarchar(300)
DECLARE @constraints cursor
SET @constraints = cursor fast_forward FOR
                   SELECT quotename(ctu.TABLE_SCHEMA) + N'.' + quotename(ctu.TABLE_NAME), quotename(cc.CONSTRAINT_NAME)
                     FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS AS cc
                     JOIN INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE AS ctu
                       ON ctu.CONSTRAINT_NAME = cc.CONSTRAINT_NAME
                    WHERE objectproperty( object_id(quotename(cc.CONSTRAINT_NAME)), 'CnstIsNotTrusted' ) = 1
OPEN @constraints
WHILE (1=1)
BEGIN
	FETCH @constraints INTO @tablename, @constraint
	IF @@fetch_status < 0 BREAK
	EXEC('ALTER TABLE ' + @tablename + ' WITH CHECK CHECK constraint ' + @constraint)
END