locked
Unique Constraint VS Check Constraint RRS feed

  • Question

  • i want to create a Unique Constraint on a SQL Server 2005 table where the uniqueness is based on 2 columns. I have following questions here

    1> Should i create Uniques constraint or Check Constraint?

    2> Whats the difference between Unque Constraint and Check Constraint?

    3> I can create Check Constraint from MS SQL Studio Manager, but NOT unique constraint why?

    4>How to create Uniqe constraint or Check Constiant for my senario above?



     

    Thursday, December 11, 2008 9:00 PM

Answers

  • A unique constraint prevents duplicate values from being inserted into a column (with one NULL allowed, if columns are nullable), where a check constraint allows you to establish a predicate condition that must be true for the one row.  To implement uniqueness with a check constraint you would need to use a user defined function to access the other rows in the table.  Usually this is only done if you want to allow N instances of a value, where N is > 1. 

     

    In your case, assuming you mean:

     

    1  1

    1  2

    1  1  <-- would not be allowed

     

    Then you should use a unique constraint.  I have no idea why you can't create one easily from managment studio, but it is easier to type:

     

    ALTER TABLE <tableName> ADD CONSTRAINT AK<tablename> UNIQUE (<columnList>)

     

    And definitely easier to reuse in different environments.

     

     

    • Proposed as answer by HunchbackMVP Saturday, December 5, 2009 8:01 PM
    • Marked as answer by Kalman Toth Wednesday, October 3, 2012 12:39 AM
    Thursday, December 11, 2008 9:25 PM
  • In Object Explorer, enter the Table Designer

    Click on Manage Indexes/Keys, Add

    Click on Columns elipsis to configure columns

    For Type, select Unique Key

    Save


    Let us know if works.


    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012


    • Edited by Kalman Toth Wednesday, October 3, 2012 12:39 AM
    • Marked as answer by Kalman Toth Wednesday, October 3, 2012 12:39 AM
    Friday, December 12, 2008 7:27 AM

All replies

  • A unique constraint prevents duplicate values from being inserted into a column (with one NULL allowed, if columns are nullable), where a check constraint allows you to establish a predicate condition that must be true for the one row.  To implement uniqueness with a check constraint you would need to use a user defined function to access the other rows in the table.  Usually this is only done if you want to allow N instances of a value, where N is > 1. 

     

    In your case, assuming you mean:

     

    1  1

    1  2

    1  1  <-- would not be allowed

     

    Then you should use a unique constraint.  I have no idea why you can't create one easily from managment studio, but it is easier to type:

     

    ALTER TABLE <tableName> ADD CONSTRAINT AK<tablename> UNIQUE (<columnList>)

     

    And definitely easier to reuse in different environments.

     

     

    • Proposed as answer by HunchbackMVP Saturday, December 5, 2009 8:01 PM
    • Marked as answer by Kalman Toth Wednesday, October 3, 2012 12:39 AM
    Thursday, December 11, 2008 9:25 PM
  • i can create Unique constraint using ALTER TABLE <tableName> ADD CONSTRAINT AK<tablename> UNIQUE (<columnList>) but i dont see that in indexes/keys option.

    Thursday, December 11, 2008 10:46 PM
  • In Object Explorer, enter the Table Designer

    Click on Manage Indexes/Keys, Add

    Click on Columns elipsis to configure columns

    For Type, select Unique Key

    Save


    Let us know if works.


    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012


    • Edited by Kalman Toth Wednesday, October 3, 2012 12:39 AM
    • Marked as answer by Kalman Toth Wednesday, October 3, 2012 12:39 AM
    Friday, December 12, 2008 7:27 AM
  • thx a bunch Kalman, that worked for me too! ;-)
    Saturday, December 5, 2009 7:43 PM