CODEWITHSUNDEEP
sqlsql-serversql-server-2008
kaushik
kaushik

Reputation: 13

constraint to make further column data unique

I have a table with some data. Now, I want to make a non unique column as unique. But the thing is, I don't want to delete the duplicate data present in the table but want to restrict the data to be added in the table from being non unique.

To be practical: I have a table tbl with name,age as columns. I have data in the table as follows:

name      |age
-----------------------
kaushikC  |21
mohan     |27
kumar     |29
mohan     |31
karthik   |55
karthik   |76

Now I want to make the name column unique without deleting the duplicate entry of 'mohan' and 'karthik'.

How to write such constraint

Upvotes: 1

Views: 100

Answers (1)

Zohar Peled
Zohar Peled

Reputation: 82534

If you have a column in your table that allows you to identify the records that you don't want to change, such as an identity column or a create date, you can create a unique filtered index on the table, specifying in it's where clause that it should only include the other records in the table.

Suppose you have an identity column called id:

id | name      |age
-----------------------
1  | kaushikC  |21
2  | mohan     |27
3  | kumar     |29
4  | mohan     |31
5  | karthik   |55
6  | karthik   |76

You could create a unique filtered index on this table that will only be valid for rows where the id is grater than 6:

CREATE UNIQUE INDEX UX_YourTable_Name_WhereIdGraterThanSix   
   ON YourTable (Name)
   WHERE id > 6;

This will enable you to keep uniqueness of other names on the table - However, it will not prevent you from inserting one more duplicate for any existing name - so you could insert another mohan or another kumar to the table (but just one).

If you want to exclude all duplicates including duplicates of existing rows, your best option is probably to use an instead of trigger for insert and update:

CREATE TRIGGER tr_YourTable ON YourTable
INSTEAD OF INSERT, UPDATE
AS
BEGIN
    -- the statement that fired the trigger is an update statement
    IF EXISTS(select 1 FROM deleted) 
    BEGIN
        UPDATE T
        SET name = I.Name
        FROM YourTable AS T
        JOIN Inserted AS I 
            ON T.Id = I.Id
        WHERE NOT EXISTS
        (   -- make sure the name is unique
            SELECT 1
            FROM YourTable AS T1
            WHERE T1.Name = I.Name
            AND NOT EXISTS
            (   -- unless it is going to be updated
                SELECT 1
                FROM Deleted AS D
                JOIN Inserted AS I
                    ON D.Id = I.Id
                WHERE D.Id = T1.Id
                AND T1.Name = D.Name
                AND D.Name <> I.Name 
            )
        )
    END
    ELSE -- the statement that fired the trigger is an insert statement
    BEGIN 
        INSERT INTO YourTable(Name)
        SELECT I.Name
        FROM Inserted I
        WHERE NOT EXISTS
        (   -- make sure the name is unique
            SELECT 1
            FROM YourTable AS T1
            WHERE T1.Name = I.Name
        )
    END
END

Upvotes: 1

Related Questions