Re: Unique constraint vs unique index

From: WangKhar (Wangkhar_at_yahoo.com)
Date: 06/11/04 

Date: 11 Jun 2004 03:06:10 -0700

I think that at a simplified level if you just want the data to be
unique use a constraint. If you want it to be unique - and you want
to have an index too slap a unique index on. Of course the unique
index also has the ignore_duplicate_key option, which can be handy for
those who don't like verifying hteir data prior to insertion or
update.

"Tom Edelbrok" <anonymous@anonymous.com> wrote in message news:<1a1yc.30$%i1.24@edtnps89>...
> 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

Relevant Pages

  • Re: Index missing after exp with TRANSPORT_TABLESPACE=Y
    ... details I sent to Oracle to help them identify the problem. ... ALTER TABLE APTPYD_REC DROP CONSTRAINT APTPYDAPTPYH0; ... ALTER TABLE APTPYH_REC DROP PRIMARY KEY; ... CREATE UNIQUE INDEX PYH_KEY1 ON APTPYH_REC ...
    (comp.databases.oracle.server)
  • Re: Why does EM script clustered indexes different ways?
    ... When you create a unique index in EM, it is by default created as a UNIQUE ... create a UNIQUE or PRIMARY KEY constraint on the column ... Creating a PRIMARY KEY or UNIQUE constraint automatically creates a unique ... > script (so i can rule out the random chance of how the engine decided to ...
    (microsoft.public.sqlserver.programming)
  • Do I need a PK on a join table?
    ... I have a client table whose PK is surrogate key client_id. ... CONSTRAINT CLIENT_ID PRIMARY KEY ); ... FOREIGN KEY REFERENCES OLTP.PRODUCT; ... CREATE UNIQUE INDEX CLIENT_PRODUCT ON CLIENT_PRODUCT ...
    (comp.databases.theory)
  • Re: Unique and non unique index
    ... Rama Shankar wrote: ... we can also create a non unique index and then add ... alter table em add (constraint pk_em primary key (empno) using index ... to make the constraint defferable or there is any other advantage also. ...
    (comp.databases.oracle.misc)
  • Re: To DISABLE foreignkeys
    ... I need to DISABLE/ENABLE all the foreign keys in a DataBase ... "Kalen Delaney" wrote: ... > unique index has to always enforce uniqueness. ... > To disable a constraint, you must use the NOCHECK option. ...
    (microsoft.public.sqlserver.server)
Loading