Difference between unique constraint and unique index
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: Difference between unique constraint and unique index

Hybrid View

  1. #1
    Join Date
    Mar 2001
    Location
    New York , New York
    Posts
    577
    Hi,

    Whats the difference between a unique constraint and a unique index

    For example the following command

    ALTER TABLE Company_lkp ADD( CONSTRAINT Company_lkp_Ticker_UNIQUE UNIQUE (Ticker) );

    or

    Create unique Index Company_lkp_Ticker_UNIQUE on Company_lkp (Ticker);

    How are the above 2 statements different?

    Thanks
    Ronnie
    ronnie_yours@yahoo.com

    You can if you think you can.

  2. #2
    Join Date
    Sep 2000
    Posts
    77
    in both cases oracle restricts from inserting duplicate values for the column on which unique constraint/index is created
    Thanks

  3. #3
    Join Date
    Mar 2001
    Location
    New York , New York
    Posts
    577
    Then why two different commands for the same thing.

    Theres got to be some difference
    Ronnie
    ronnie_yours@yahoo.com

    You can if you think you can.

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    The main difference is if you need to reference the unique constrainted column with some foreign key. For that purpose you need unique CONSTRAINT, unique INDEX will not be enough.

    So for ensuring uniqueness both methods will do the same job. but if you need a foreign key referencing that unique column (or combination of columns) you need UNIQUE KEY constraint.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  5. #5
    Join Date
    Nov 1999
    Posts
    226
    The Unique Index is used for Data Integrity and More for Data Access While The unique Constraint is for Data Integrity only

    Hope this helps

    Regards

  6. #6
    Join Date
    Mar 2001
    Location
    New York , New York
    Posts
    577
    Originally posted by jmodic
    The main difference is if you need to reference the unique constrainted column with some foreign key. For that purpose you need unique CONSTRAINT, unique INDEX will not be enough.

    So for ensuring uniqueness both methods will do the same job. but if you need a foreign key referencing that unique column (or combination of columns) you need UNIQUE KEY constraint.

    Doesnt a foreign key reference need a primary key to point at, and not a unique constraint. (even though primary key is a unique constraint also).

    Also will creating a unique constraint also automatically create a index.

    Thanjs
    Ronnie
    ronnie_yours@yahoo.com

    You can if you think you can.

  7. #7
    Join Date
    Aug 2001
    Posts
    75
    Question : Doesnt a foreign key reference need a primary key to point at, and not a unique constraint. (even though primary key is a unique constraint also).
    Ans:The foreign key could reference to Unique key as well as Primary key.

    Question:Also will creating a unique constraint also automatically create a index.
    Ans: Yes, it will create index.

    Thanks
    Sanjay
    OCP 8i

  8. #8
    Join Date
    Mar 2002
    Posts
    171
    Let me tell the major difference:

    Unique constraint only takes care to see that only unique values are entered, whereas Unique Index creates a seperate Index Block in which data is indexed as well. In the latter case, when the column is referenced in the where clause -- the BTREE search is instantiated so that the execution is very fast. With a unique key alone, its not a base for a good tuned system.


  9. #9
    Join Date
    Mar 2001
    Location
    New York , New York
    Posts
    577
    Originally posted by dbafreak
    Let me tell the major difference:

    Unique constraint only takes care to see that only unique values are entered, whereas Unique Index creates a seperate Index Block in which data is indexed as well. In the latter case, when the column is referenced in the where clause -- the BTREE search is instantiated so that the execution is very fast. With a unique key alone, its not a base for a good tuned system.


    Does this mean that a unique constraint does not create an Index. (but primary key constraint does)
    Ronnie
    ronnie_yours@yahoo.com

    You can if you think you can.

  10. #10
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by ronnie
    Does this mean that a unique constraint does not create an Index. (but primary key constraint does)
    No.

    Both UK and PK constraint must have underlying index to enforce uniqueness. So if the appropriate index (either unique or nonunique) does not allready exist when you create the PK or UK constraint the unique index will be created automaticaly when you create the constraint.

    The mayor differences between UK and Pk are:
    - there could only be one PK per table, while there could be many UK per table
    - for PK, all columns included in the key must be NOt NULL, while UK can incorporate also nullable columns
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

Posting Permissions

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



Click Here to Expand Forum to Full Width