Clever Geek Handbook

Check if a column of a table has a unique constraint

I need a query that can tell me if a table column has a unique constraint or not. If it doesn't, I must add a unique constraint. I am currently using below query to check if a column of a table has a unique constraint:

IF NOT EXISTS (SELECT *
    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
    WHERE TC.CONSTRAINT_TYPE = 'UNIQUE' AND CONSTRAINT_NAME = 'IX_Product_Users' AND TABLE_NAME = 'Product_Users')
BEGIN
    ALTER TABLE Product_Users
    ADD CONSTRAINT IX_Product_Users UNIQUE (EmpID)
END
GO

Tried this one too, but it can't check which column this constraint is on:

SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE TABLE_NAME='Product_Users' AND CONSTRAINT_TYPE = 'UNIQUE'

But I think this is the wrong way because it is possible that the unique name of the constraint is different. Is there any specific way to do this?

+3


source to share


1 answer


Instead of using the constraint name, find the same definition. something like



SELECT * 
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc 
    inner join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE cu 
        on cu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME 
where 
    tc.CONSTRAINT_TYPE = 'UNIQUE'
    and tc.TABLE_NAME = 'Product_Users'
    and cu.COLUMN_NAME = 'EmpID'
+4


source






More articles:


All Articles