April 22, 2016 at 11:51 pm
Hi
There is a table which has one field with some duplicate values.
we can not drop or update these duplicate values and have accepted them.
But after now, we have to prevent insert duplicate keys.
can I do this by check constraints? this is the best way for me if possible .
And if It is not possible what is the second option?
thank you
April 23, 2016 at 12:11 am
My guess is that you would have to use a stored procedure and check for existence before doing your insert(s). The problem is that you can't declare something has to be unique from now on.
April 23, 2016 at 1:11 pm
How can you determine if a new value is unique if you already have duplicates?
What would be your definition? Remember that there is no order to rows, so you can't say that a row is new.
However, what's new over time? If I have 1, 2, 3, 2 in a column and I add a 3, is that a dupe? Sure. If I add a "2", is that a dupe?
What's your logic?
April 23, 2016 at 1:41 pm
If there is anything in the row (in another column) that defines it as new, then you could do part of your requirement by using a filtered index:
CREATE UNIQUE INDEX
ON dbo.MyTable(MyColumn)
WHERE IsNew = 1;
That would still allow a new row to be inserted that has a value in MyColumn that exists in an old row, but it will not allow a new row with a value that exists in another new row.
For a full check, you can write a user-defined function (UDF) that tests if a duplicate was inserted, then use that in a CHECK constraint - but note that this will severely impact performance of multi-row insert and update operations since it has to execute the UDF for each affected row. Also note the order of execution: SQL Server first makes the change, then executes the UDF, and then rolls back the change if a violation occurs. So you cannot simply do an EXISTS for the value, as that will always return true.
Assuming you have another column (KeyColumn) that uniquely identifies the row, the code would be something like the below (untested):
CREATE FUNCTION dbo.MyCheck(@KeyValue int, @CheckValue varchar(20))
RETURNS bit
AS
BEGIN;
IF EXISTS (SELECT * FROM dbo.MyTable WHERE MyColumn = @CheckValue AND KeyColumn <> @KeyValue)
RETURN 1;
ELSE
RETURN 0;
END;
go
ALTER TABLE dbo.MyTable
ADD CONSTRAINT CK_NoDuplicates CHECK (dbo.MyCheck (KeyColumn, MyColumn) = 0);
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply