Home » SQL & PL/SQL » SQL & PL/SQL » having two PK for a table
having two PK for a table [message #222592] Mon, 05 March 2007 10:01 Go to next message
miroconnect@yahoo.com
Messages: 202
Registered: April 2006
Senior Member
Suppose I have two primary keys for a table

col1 -values(a,b,c,d,e) col2 -values(1,2,3,4,5)

I know PK are unique and not null.

If two columns are PK
how does oracle enforce restrictions ?

I assume this table will allow a row with a,5 but not a row with a,1 because a,1 combination already exsists.
Is my assumption correct ?
Re: having two PK for a table [message #222594 is a reply to message #222592] Mon, 05 March 2007 10:08 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
A piece of heartfelt advice: Try things for yourself. You'll learn more and remember the results for longer, and these things are SOOOOOOooooo easy to test.
SQL> create table PK_test (col_1 number, col_2 number);

Table created.

SQL> alter table  pk_test add constraint pk_Test_pk_1 primary key (col_1);

Table altered.

SQL> alter table  pk_test add constraint pk_Test_pk_2 primary key (col_2);
alter table  pk_test add constraint pk_Test_pk_2 primary key (col_2)
                                                 *
ERROR at line 1:
ORA-02260: table can have only one primary key
Re: having two PK for a table [message #222858 is a reply to message #222592] Tue, 06 March 2007 13:48 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
However, you can, in effect, do what you want. Setup col1 as your primary key and col2 an a unique not null index which will have the same effect.
Re: having two PK for a table [message #222975 is a reply to message #222858] Wed, 07 March 2007 02:00 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
True - and you can point Foreign keys at the Unique key too.

That does raise the question of exactly what the difference is between that and a Primary Key.
Previous Topic: Cursor Memory Reserve
Next Topic: Complex query hangs when selecting from table where fresh data is inserted
Goto Forum:
  


Current Time: Thu Feb 06 11:05:56 CST 2025