Select to get 2 columns constraint

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?

    
asked by anonymous 06.04.2018 / 20:29

1 answer

1
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.

    
07.04.2018 / 04:00