Home » SQL & PL/SQL » SQL & PL/SQL » Creating primary key (10.2.0.3 on Solaris 8)
Creating primary key [message #379220] Mon, 05 January 2009 08:41 Go to next message
orausern
Messages: 817
Registered: December 2005
Senior Member
Hi,

I have a question about the proper way of creating primary keys.
Earlier we were on Oracle 9i and we upgraded to Oracle 10g. The question is when creating primary key is it ok to first create the unique index and then to create primary key constraint? or directly create the primary key is better? or are both these ways one and the same?

One sample syntax is:
one way:

SQL> CONN u2/u2@db1
Connected.
SQL> create table a(a1 number,b1 varchar2(2),c1 date);

Table created.

SQL> create unique index pk_a on a(a1) tablespace t1;

Index created.


SQL>  alter table a add(constraint pk_a primary key (a1) using index);

Table altered.
another way:
SQL> CONN u1/u1@db1
Connected.
SQL> create table a(a1 number,b1 varchar2(2),c1 date)
  2  /

Table created.

SQL> 
SQL> alter table a add(constraint pk_a primary key (a1) using index tablespace t1)
  2  /

Table altered.

[Updated on: Mon, 05 January 2009 08:44]

Report message to a moderator

Re: Creating primary key [message #379221 is a reply to message #379220] Mon, 05 January 2009 08:44 Go to previous messageGo to next message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
Please note that you can create a non-unique index and then a primary key based off of that. A primary key constraint does not need a unique index.
Re: Creating primary key [message #379222 is a reply to message #379221] Mon, 05 January 2009 08:45 Go to previous messageGo to next message
orausern
Messages: 817
Registered: December 2005
Senior Member
So which of the two approaches is better or recommended one?
Re: Creating primary key [message #379224 is a reply to message #379222] Mon, 05 January 2009 08:51 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
There's no real advantage one way or the other - you end up with exactly the same functionality in both cases.
Re: Creating primary key [message #379225 is a reply to message #379224] Mon, 05 January 2009 09:02 Go to previous messageGo to next message
orausern
Messages: 817
Registered: December 2005
Senior Member
A strange thing I noted after Joy's reply...I allways thought that primary keys are based on unique index but if I give normal index than primary key is created using that too! Now when I don't create the index explicitly, then (in that way) Oracle implicitly created a unique index!! so its not the same!!!


SQL> SELECT UNIQUENESS from user_indexes where table_name='A';

UNIQUENES
---------
UNIQUE

SQL> CONN ...CONNECT TO SECOND USER...
Connected.
SQL> /

UNIQUENES
---------
NONUNIQUE



Re: Creating primary key [message #379226 is a reply to message #379225] Mon, 05 January 2009 09:13 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
No, the indexes are not the same.
The functionality provided by the database however, is identical.
Previous Topic: funcation date (-)
Next Topic: Counts for all dates in a given month
Goto Forum:
  


Current Time: Thu Dec 08 22:19:43 CST 2016

Total time taken to generate the page: 0.05824 seconds