SysIndex - difference b/w is_unique and is_unique_constraint

Question

runing a select query on SQL Server 2008, sys.indexes gives me information on the index define for a database .

There 2 fields is_unique and is_unique_constraint. I dont understand the difference b/w them .

Answer 1

Hopefully, this simple demo will make things clearer for you. The index on table X will have both values set, while the index on table Y will have only is_unique set.

create table X (
    id int CONSTRAINT x_is_unique UNIQUE
)

create table Y (
    id int
)

create unique index y_is_unique on Y(id)

select name, is_unique, is_unique_constraint
    from sys.indexes
    where object_id in (object_id('X'), object_id('Y'))
        and name is not null

drop table X
drop table Y