| Difference between primary key and unique index [message #388152] |
Mon, 23 February 2009 08:46  |
prashas_d
Messages: 66
Registered: February 2007
|
Member |
|
|
Hi All,
When we define a primary key on a table, Oracle internally creates an index on the columns defined in the primary key constraint.
The alternate ways of creating index are
2) Without creating a primary key, we can also create a unique index using "CREATE UNIQUE INDEX" clause.
3) Also, while creating a primary key, we can explicitly state index as "ADD CONSTRAINT <constraint_name> PRIMARY KEY (<column_name>) USING INDEX <index_name>".
Normally, I simply use the first one where I define a primary key and Oracle internally creates an index.
Can someone please let me know whether there will be any performance differences between any of the three above mentioned methods?
Thanks in advance,
prashas_d.
|
|
|
|
|
|
|
|
| Re: Difference between primary key and unique index [message #388214 is a reply to message #388203] |
Mon, 23 February 2009 19:34  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
- A PK can be disabled (no index created)
- A PK adds NOT NULL constraints to the PK columns
- A PK can be enforced by any B-Tree index with the key columns as leading columns - even non-unique indexes.
- You cannot reference a foreign key constraint to a simple unique index.
- You sometimes need a PK to use Materialized View fast Refresh
- PK's can help some applications - like Application Express - automatically write code for you.
Ross Leishman
|
|
|
|
|
|
| Re: Difference between primary key and unique index [message #388329 is a reply to message #388214] |
Tue, 24 February 2009 06:16 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
| Quote: | - You cannot reference a foreign key constraint to a simple unique index.
|
That's not the case in 10g. (unless I'm misunderstanding what you're saying)
create table test_112 (col_1 number, col_2 number, constraint test_112_uk unique(col_1));
create table test_113 (col_1 number, constraint test_113_fk foreign key(col_1) references test_112(col_1));
insert into test_112 values (1,1);
insert into test_113 values (1);
insert into test_113 values (2); |
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
