Difference between unique constraint and unique index

[ronnie]

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

[CHALAVADI]

in both cases oracle restricts from inserting duplicate values for the column on which unique constraint/index is created

[ronnie]

Then why two different commands for the same thing.

Theres got to be some difference

[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.

[puneet]

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

[ronnie]

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

[sanjay92]

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

[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.

[ronnie]

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)

[jmodic]

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