Dismiss Notice
Join Physics Forums Today!
The friendliest, high quality science and math community on the planet! Everyone who loves science is here!

SQL unique id

  1. Jul 27, 2016 #1
    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.
  2. jcsd
  3. Jul 28, 2016 #2
    Seems so, according to MSDN:https://technet.microsoft.com/en-us/library/ms174321(v=sql.110).aspx
    This gives me sensible results:

    Code (Text):

    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.)
  4. Jul 28, 2016 #3
    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.
  5. Jul 28, 2016 #4
    Not sure what you mean; certainly I was just looking at the specs of your own question.
  6. Jul 28, 2016 #5

    jim mcnamara

    User Avatar

    Staff: Mentor

    Do you mean index metadata is redundant or maybe not normalized?
  7. Jul 29, 2016 #6
    I only find constraint information is still obtainable even without sys.key_constraints.
Share this great discussion with others via Reddit, Google+, Twitter, or Facebook

Have something to add?
Draft saved Draft deleted