Re: composite primary keys

From: <fitzjarrell_at_cox.net>
Date: Mon, 08 Oct 2007 09:08:14 -0700
Message-ID: <1191859694.512695.146520@y42g2000hsy.googlegroups.com>

On Oct 8, 10:44 am, g.mrunal..._at_gmail.com wrote:
> What is the syntax for composite primary key to a table. The table
> already contains primary key as one column and I need to create
> composite primary key for three other columns in the same table.

You can have but ONE primary key per table; as you already have one defined it will need to be dropped and the new key created. But I think this is not what you intend; possibly you want a UNIQUE constraint on these three other columns, keeping your currently defined primary key intact? As an example:

SQL> create table mytable (my_id number,

  2                        mycol varchar2(10),
  3                        mynextcol varchar2(20),
  4                        mynextkey number,
  5                        myothercol varchar2(15));

Table created.

SQL>
SQL>
SQL> create sequence mytable_seq

  2 start with 1 increment by 1 nomaxvalue nocycle nocache order;

Sequence created.

SQL>
SQL> create or replace trigger mytable_id_trg   2 before insert on mytable
  3 for each row
  4 begin

  5          select mytable_seq.nextval
  6          into :new.my_id
  7          from dual;

  8 end;
  9 /

Trigger created.

SQL>
SQL> alter table mytable
  2 add constraint mytable_pk primary key(my_id);

Table altered.

SQL>
SQL> alter table mytable
  2 add constraint mytable_uq unique (mynextcol, mynextkey, myotherco

Table altered.

SQL>
SQL> insert into mytable
  2 (mycol, mynextcol, mynextkey, myothercol)   3 values
  4 ('Test','Test',1,'Test');

1 row created.

SQL>
SQL> insert into mytable
  2 (mycol, mynextcol, mynextkey, myothercol)   3 values
  4 ('Test','Test',2,'Test');

1 row created.

SQL>
SQL> insert into mytable
  2 (mycol, mynextcol, mynextkey, myothercol)   3 values
  4 ('Test','Test',3,'Test');

1 row created.

SQL>
SQL> insert into mytable
  2 (mycol, mynextcol, mynextkey, myothercol)   3 values
  4 ('Test','Test',4,'Test');

1 row created.

SQL>
SQL> insert into mytable
  2 (mycol, mynextcol, mynextkey, myothercol)   3 values
  4 ('Test','Test',1,'Test');
insert into mytable
*
ERROR at line 1:
ORA-00001: unique constraint (BING.MYTABLE_UQ) violated

SQL>
SQL> insert into mytable
  2 (mycol, mynextcol, mynextkey, myothercol)   3 values
  4 ('Test','Test',5,'Test');

1 row created.

SQL>
SQL> select my_id, mycol, mynextcol, mynextkey, myothercol   2 from mytable;

     MY_ID MYCOL MYNEXTCOL MYNEXTKEY MYOTHERCOL

---------- ---------- -------------------- ---------- ---------------
         1 Test       Test                          1 Test
         2 Test       Test                          2 Test
         3 Test       Test                          3 Test
         4 Test       Test                          4 Test
         6 Test       Test                          5 Test

SQL> Is this what you have in mind, rather than trying to create two primary keys on one table?

David Fitzjarrell Received on Mon Oct 08 2007 - 11:08:14 CDT