Unique_index_id Value in sys.key_constraints & is_unique Column in sys.indexes

[Pepper Mint]

I find in SQL Server, sys.indexes contains an is_unique column and sys.key_constraints contains unique_index_id. Could you help confirm me that the unique_index_id value in the latter is also the index_id value of the former when its is_unique becomes true or 1 ?

Thank you.

 

[sdkfz]

Seems so, according to MSDN:https://technet.microsoft.com/en-us/library/ms174321(v=sql.110).aspx

ID of the corresponding unique index in the parent object that was created to enforce this constraint.

This gives me sensible results:

select      Object_Name(I.object_id) ObjectName, I.name IName, C.name ConstraintName
    ,       I.type_desc IndexType, C.type_desc ConstraintType
from        sys.indexes I
left join   sys.key_constraints C
    on      I.object_id = C.parent_object_id
    and     i.index_id = C.unique_index_id
where       I.is_unique = 1
    and     not c.object_id is null

(Slightly odd SQL as I was running it in bits (not always all of it at once) to see effects.)

 

[Pepper Mint]

Seems so, according to MSDN:https://technet.microsoft.com/en-us/library/ms174321(v=sql.110).aspx

Thanks! I just feel something redundant in SQL Server.

This gives me sensible results:

select      Object_Name(I.object_id) ObjectName, I.name IName, C.name ConstraintName
    ,       I.type_desc IndexType, C.type_desc ConstraintType
from        sys.indexes I
left join   sys.key_constraints C
    on      I.object_id = C.parent_object_id
    and     i.index_id = C.unique_index_id
where       I.is_unique = 1
    and     not c.object_id is null

(Slightly odd SQL as I was running it in bits (not always all of it at once) to see effects.)

uhmm..., despite of the bits you are doing, I think this query is generally correct but not enough to cover all cases of various DB designs.

 

[sdkfz]

Thanks! I just feel something redundant in SQL Server.uhmm..., despite of the bits you are doing, I think this query is generally correct but not enough to cover all cases of various DB designs.

Not sure what you mean; certainly I was just looking at the specs of your own question.

 

[jim mcnamara]

Do you mean index metadata is redundant or maybe not normalized?

 

[Pepper Mint]

Do you mean index metadata is redundant or maybe not normalized?

I only find constraint information is still obtainable even without sys.key_constraints.