Unique constraints - WITH NOCHECK?

[daglugub37]

I would like to alter a table and add a unique constraint to a combination of 3 columns. I know there are several records present that would violate this constraint. Can I ignore those present records and apply the constraint to be used for future inserts.

Here is what I tried.

ALTER TABLE tblX WITH NOCHECK
ADD CONSTRAINT uk_constraint_name
UNIQUE (colB, colC, colD)

It returned a "can not create unique index" error...due to duplicates.

So it would appear that for unique constraints you can NOT ignore duplicates..and that a unique index will be created auotmatically as well....kind of like a PK constraint.

Is this so?

 

[JohnDTampaBay]

From BOL: UNIQUE constraints, Creating and Modifying UNIQUE Constraints:

When a UNIQUE constraint is added to an existing column or columns in the table, Microsoft® SQL Server™ 2000 by default checks the existing data in the columns to ensure all values, except NULL, are unique. If a UNIQUE constraint is added to a column that has duplicated values, SQL Server returns an error and does not add the constraint.

SQL Server automatically creates a UNIQUE index to enforce the uniqueness requirement of the UNIQUE constraint.

The WITH NOCHECK is apparently ignored, like when a table is altered to add a primary key constraint.

--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)