I am using

ALTER TABLE [dbo].[Table] ADD
CONSTRAINT [Field1_Field2] UNIQUE NONCLUSTERED
(
[Field1],
[Field2]
) ON [PRIMARY]

To create a constraint so that Field1 and Field2 together are unique.

This works fine. However as these are existing tables if there is data which contradicts these rules the ALTER TABLE fails.


Is there a way to force thu the update - so it deletes the non unique data?


Thanks

Hi there, try this


ALTER TABLE [dbo].[Table]with NOCHECK ADD
CONSTRAINT [Field1_Field2] UNIQUE NONCLUSTERED
(
[Field1],
[Field2]
) ON [PRIMARY]

This will let the data into the table but not delete the non conforming data, to do this you will need to query the data and delete, you will also need to upt the contraint back to status check from nocheck

Hi SQL_Hell

I tried your solution above - but it still errors if data already in the table goes against the constraint I am trying to add.

(I have tested it and it is defiantely the above that causes the fail)

Any further ideas - at the moment I Have just left the constraint off the table.

Thanks