adding composite unique constraint with no check

[Crowley16]

Hi

I'm trying to add a unique constraint to a table on 2 seperate columns, however even with the "with nocheck" specified, the system says I can't add it because of duplicate key

the sql I have now is:

alter table T_Location with nocheck
   add constraint [CK_T_Location_UniqueObj] UNIQUE (Loc_Tpe, Loc_IntID)
GO

Loc_Tpe is a varchar and Loc_IntID is an int, and both would have duplicates in it individually, but should be unique together (however there's some non-unique ones at the moment)

I'm sure the syntax is correct, so why is the nocheck not working?

Thanks guys/gals...

--------------------
Procrastinate Now!

 

[carlen]

Keep in mind that you can't violate the rules for creating a primary key on a table if it's already got data. If you use a current column as a key, it has to have unique values on it.

In the case of the primary key, if you have a repeated value in a column, applying the primary key constraint on that column the statement will fail. However, if you apply the constraint with the WITH NOCHECK statement, SQL Server will apply the constraint (and do it very quickly, I might add); but the very next time you load or alter data, that change will fail and the table will lock.

If that happens, you'll have to change the table by removing the primary key and then fix the data. So the short story is that you shouldn't use the WITH NOCHECK qualifier unless you're certain the data works with the constraint first. Primary keys enforce entity integrity, meaning that a column is guaranteed to be unique. The system simply won't let you enter duplicate data in a primary key column.

 

[Crowley16]

neither of the columns are the primary key

I just want these to combined to be unique hence the UNIQUE key work and not PRIMARY...

--------------------
Procrastinate Now!

 

[Crowley16]

I've tidied up the data and afterwards it created it fine...

--------------------
Procrastinate Now!