Post reply

difference between unquie and PK constraint

  • November 18, 2011 at 3:30 am

    #96617

    I understand that unique constraint makes sure that each values in a column are unique (allows no duplicates?). PK constraint uses the value(s) in a column or columns to uniquely identify each rows.... Of course PK constraint precludes NULL in the PK constrainted column(s). What exactly are the difference(s) between the two constraints?

  • November 18, 2011 at 3:48 am

    #1409779

    You can have one pk on a table, but multiple unique. PK requires not-nullable, unique can be on a nullable column, in which case one null is allowed. Transactional repl requires PK constraints, unique are not good enough.

    By default, pk constraint is enforced by a clustered index and unique by nonclustered, but that's by default only.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • November 18, 2011 at 4:09 am

    #1409789

    Thanks GilaMonster.

    I'd like you to confirm the below two highlights which I believe are what you are saying:

    1) Unique constraint allows ONE NULL value per column where Unique constraint is defined.

    2) Both unique and PK constraint precludes duplicate values.

    Correct?

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply