CODEWITHSUNDEEP
sql-server
user3756799
user3756799

Reputation: 91

Check if Unique Key is Composite or not in SQL Server

Alter table <table_name> 
    add constraint <constraint_name> unique (<column1, column 2>);

I alter the table and add a unique constraint called constraint_name. Is there a way to check if this constraint_name is composite or not, and if composite, then find the columns from which it is made?

Upvotes: 2

Views: 113

Answers (1)

TheGameiswar
TheGameiswar

Reputation: 28890

select k.name,k.type,k.type_desc,k.object_id
,col.name
 from sys.key_constraints k
 join
 sys.index_columns ic on ic.object_id=k.parent_object_id
  and ic.object_id= object_id('t1')
  join
  sys.all_columns col
  on col.column_id=ic.column_id
  and ic.object_id=col.object_id

gives me below

name    type    type_desc   object_id   name
t11 UQ  UNIQUE_CONSTRAINT   1221579390  id
t11 UQ  UNIQUE_CONSTRAINT   1221579390  id1

if the constraint is made up of more than one key,than it is composite.. you can do a count like below and check whether its composite or not

;with cte
as
(
select k.name,k.type,k.type_desc,k.object_id
,col.name as colname,count(k.name) over (partition by  k.name)  as cnt
from sys.key_constraints k
join
sys.index_columns ic on ic.object_id=k.parent_object_id
and ic.object_id= object_id('t1')
join
sys.all_columns col
on col.column_id=ic.column_id
and ic.object_id=col.object_id
)

select *,case when cnt>=2 then 'composite' else 'not composite' end as 'Check'
from cte

for this sample data

create table t1
(
id int,
id1 int
)


Alter table t1 add constraint t11 unique (id,id1);

Upvotes: 1

Related Questions