-
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.
-
in both cases oracle restricts from inserting duplicate values for the column on which unique constraint/index is created
Thanks
-
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.
-
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?
-
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
-
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.
-
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
-
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.
-
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.
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|