Home » SQL & PL/SQL » SQL & PL/SQL » Difference between primary key and unique index (Oracle 10g)
Difference between primary key and unique index [message #388152] Mon, 23 February 2009 08:46 Go to next message
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 #388161 is a reply to message #388152] Mon, 23 February 2009 09:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
A primary key is a DESIGN AND FUNCTIONAL notion.
Oracle and its optimizer knows the theorical meaning of a primary key and so can efficiently use it.
An index is JUST a way to fasten access to data.

If you functionaly have a primary, create a primary key.

Regards
Michel

[Updated on: Mon, 23 February 2009 09:53]

Report message to a moderator

Re: Difference between primary key and unique index [message #388203 is a reply to message #388161] Mon, 23 February 2009 16:39 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
Quote:
An index is JUST a way to fasten access to data.


Careful with that statement. You'll have people thinking that you are saying (I know that you're not)that an index will always make queries faster.
Re: Difference between primary key and unique index [message #388214 is a reply to message #388203] Mon, 23 February 2009 19:34 Go to previous messageGo to next message
rleishman
Messages: 3724
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 #388248 is a reply to message #388203] Tue, 24 February 2009 00:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
pablolee wrote on Mon, 23 February 2009 23:39
Quote:
An index is JUST a way to fasten access to data.


Careful with that statement. You'll have people thinking that you are saying (I know that you're not)that an index will always make queries faster.

Thanks for the precision.

Regards
Michel

Re: Difference between primary key and unique index [message #388329 is a reply to message #388214] Tue, 24 February 2009 06:16 Go to previous messageGo to next message
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);
Re: Difference between primary key and unique index [message #388330 is a reply to message #388329] Tue, 24 February 2009 06:21 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
JRowbottom wrote on Tue, 24 February 2009 13:16
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 [B]unique(col_1)[/B]);

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);



That's an unique constraint, which is, imho, different from an unique index
Re: Difference between primary key and unique index [message #388335 is a reply to message #388330] Tue, 24 February 2009 06:32 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Well, that clears that up neatly.

I did think Ross was unlikely to have made a mistake like that - seems I need more sleep and/or coffee
Re: Difference between primary key and unique index [message #388337 is a reply to message #388335] Tue, 24 February 2009 06:37 Go to previous message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
JRowbottom wrote on Tue, 24 February 2009 13:32
Well, that clears that up neatly.

I did think Ross was unlikely to have made a mistake like that - seems I need more sleep and/or coffee


http://www.websmileys.com/sm/drink/trink33.gif
Previous Topic: sending attachments using smtp
Next Topic: use substr/instr [Merged]
Goto Forum:
  


Current Time: Sat Dec 10 20:21:13 CST 2016

Total time taken to generate the page: 0.32767 seconds