trying to add composite key to existing data 1

[clanm]

We have a table and I'm trying to regulate the duplication of one column, since the business rules have now changed. I want to add a composite Unique Constraint Index.

The column I'm looking @ is ActID. We now want to enforce this doesn't get duplicated in conjunction with another column, Work ID.

the layout/schema is the Work ID table(parent) links to the ActID table(child) via the Work ID. What we want to block is any further inserts where duplicate ActID will appear per work ID.

I'm trying to add this to a table, but the constraint fails because the previous business rule allowed the duplication of ActIDs per same WorkID.

I don't see anything via the Properties table where I can ignore existing data.....like the Constraints tab does.

CONSTRAINT [IX_WP_Activities_1] UNIQUE NONCLUSTERED
(
[WorkID],
[ActID]
)
Any ideas are welcome!

Thanks!

 

[alaniane]

You can't disable a UNIQUE constraint. You will have to eliminate the duplicates before you apply constraint.

 

[clanm]

Thanks alaniane!

yeah, we're going to clean up the data then add the constraint.