Select to get 2 columns constraint

Question

Select to get 2 columns constraint

Navigation

#1 by (1 votes)

0

I have a PERSON table that has field A and B that form a UNIQUE .

I squeeze this select to return the name of the constraint :

SELECT DISTINCT COL.CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS Tab, INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE Col
WHERE Col.Constraint_Name = Tab.Constraint_Name
AND Col.Table_Name = Tab.Table_Name
AND Col.Table_Name = 'PESSOA'
AND COL.COLUMN_NAME IN ('A','B')
AND Tab.CONSTRAINT_TYPE = 'UNIQUE'

The problem is that if there is an individual UNIQUE for both fields it is returned as well. How do pro select return only the constraint that is associated with columns A and B?

sql sql-server

asked by anonymous 06.04.2018 / 20:29

1 answer

How to add two li in one ul dynamically with jQuery? WPF - Access UserControl from a selected TabItem

score 1 · Accepted Answer

SELECT COL.CONSTRAINT_NAME 
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS Tab, INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE Col
WHERE Col.Constraint_Name = Tab.Constraint_Name
AND Col.Table_Name = Tab.Table_Name
AND Col.Table_Name = 'PESSOA'
AND COL.COLUMN_NAME IN ('A','B')
AND Tab.CONSTRAINT_TYPE = 'UNIQUE
GROUP BY COL.CONSTRAINT_NAME
HAVING COUNT(*) > 1

It will bring cases of more than one column.