Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2003
    Posts
    268

    Unanswered: What is the difference between a UNIQUE INDEX and a UNIQUE CONSTRAINT?

    A UNIQUE INDEX must inherently impose a unique constraint and a UNIQUE CONSTRAINT is most likely implemented via a UNIQUE INDEX. So what is the difference? When you create in Enterprise Manager you must select one or the other.

  2. #2
    Join Date
    Sep 2003
    Location
    Dallas, Texas
    Posts
    311
    See BOL titled 'Using Unique Indexes'. It has a whole section talking about when you should create an index and when you should use a constraint.

  3. #3
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    True, BOoks online is the best and first resource to findout such information.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by BOL

    Considerations

    Specifying a unique index makes sense only when uniqueness is a characteristic of the data itself. If uniqueness must be enforced to ensure data integrity, create a UNIQUE or PRIMARY KEY constraint on the column rather than a unique index. For example, if you plan to query frequently on the Social Security number (ssn) column in the employee table (in which the primary key is emp_id), and you want to ensure that Social Security numbers are unique, create a UNIQUE constraint on ssn. If the user enters the same Social Security number for more than one employee, an error is displayed.
    Yeah, that really clears things up.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Sep 2003
    Posts
    364
    When a unique constraint is created it essentially creates a unique index. The most significant diff IMHO is the use of ALTER TABLE for unique constraints.

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    The bigger thing is the misconception that a constraint doesn't generate the index....

    Now the question....is the index for every row or not?

    Or is it a set of distinct values...like a CHECK?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    A constraint produces metadata for the table, which can be used by both the optimizer and by many kinds of ODBC based applications (particularly report generation tools). An index is created either way, because the index is the mechanism that enforces the uniqueness of rows for SQL Server.

    In my opinion, the constraint is always the better choice, because it generates additional information with no material cost.

    -PatP

  8. #8
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    I believe (but have not tested it out) that you can have a foreign key reference a unique constraint, but not a unique index. I have not seen a lot of data models that would require such a thing, though.

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by MCrowley
    I believe (but have not tested it out) that you can have a foreign key reference a unique constraint, but not a unique index. I have not seen a lot of data models that would require such a thing, though.
    [homer]
    dooooooooooh
    [/homer]

    But of course..what a Maroon

    Code:
    USE Northwind
    GO
    
    SET NOCOUNT ON
    CREATE TABLE myTable99(Col1 int IDENTITY(1,1) PRIMARY KEY, LastName varchar(50) UNIQUE)
    GO
    
    INSERT INTO myTable99(LastName) SELECT 'Bush' UNION ALL SELECT 'Kerry' UNION ALL SELECT 'Nader'
    GO
    
    CREATE TABLE myTable00(Col1 int IDENTITY(1,1) PRIMARY KEY, LastName varchar(50)
    	, FOREIGN KEY (LastName) 
    	  REFERENCES myTable99(LastName))
    GO
    
    CREATE TABLE myTable88(Col1 int IDENTITY(1,1) PRIMARY KEY, LastName varchar(50))
    GO
    
    INSERT INTO myTable88(LastName) SELECT 'Bush' UNION ALL SELECT 'Kerry' UNION ALL SELECT 'Nader'
    GO
    
    -- Will Fail
    --CREATE TABLE myTable01(Col1 int IDENTITY(1,1) PRIMARY KEY, LastName varchar(50)
    --	, FOREIGN KEY (LastName) 
    --	  REFERENCES myTable88(LastName))
    -- With:
    -- Server: Msg 1776, Level 16, State 1, Line 1
    -- There are no primary or candidate keys in the referenced table 'myTable88' that match the referencing column list in the foreign key 'FK__myTable01__LastN__26A5A303'.
    -- Server: Msg 1750, Level 16, State 1, Line 1
    -- Could not create constraint. See previous errors.
    GO
    
    SET NOCOUNT OFF
    DROP TABLE myTable00
    --DROP TABLE myTable01
    DROP TABLE myTable99
    DROP TABLE myTable88
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •