Unique constraint vs unique index

From: Tom Edelbrok (anonymous_at_anonymous.com)
Date: 06/10/04

  • Next message: WangKhar: "Re: Unique constraint vs unique index" 
    Date: Thu, 10 Jun 2004 17:48:45 GMT

    What is the difference between using a unique constraint and a unique index?
    The Microsoft SQL Books Online says:

    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 (where 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.

    So what does it mean "a characteristic of the data itself"? What does SQL do
    differently when you choose one method over the other? In general, when I
    want a column in a table to be unique, how can I know whether to make a
    unique constraint or a unique index?

    Thanks in advance,

    Tom

  • Next message: WangKhar: "Re: Unique constraint vs unique index"

    Relevant Pages

    • Re: Difference between creating a Unique Constraint and a Unique Index
      ... I have two real life examples where I would use a unique index as ... ,CONSTRAINT PK_StudentCourses PRIMARY KEY (StudentID, CourseID) ... In this case there already is a unique constraint on (StudentID, ...
      (microsoft.public.sqlserver.server)
    • Re: Unique index vs. unique constraint
      ... Neron wrote: ... come across information that Oracle deprecated creating unique index ... triggers and helper columns, ... the recommendation to use a unique constraint rather ...
      (comp.databases.oracle.server)
    • Re: UNIQUE index - constraint BUG/FEATURE?
      ... SQL Server 2000 by defining an indexed view as follows: ... testId charNULL, ... > No two rows in the table are allowed to have the same not null values for> the columns in a UNIQUE constraint. ... A UNIQUE constraint is preferred> over a unique index. ...
      (microsoft.public.sqlserver.programming)
    • UNIQUE index - constraint BUG/FEATURE?
      ... UNIQUE constraints enforce the uniqueness of the values in a set of columns. ... the columns in a UNIQUE constraint. ... Cannot insert duplicate key row in object 'testTable' with unique index ... wich is opposite to what is stated in the SQL BOL. ...
      (microsoft.public.sqlserver.programming)
    Loading