Creating primary key and assigning unique constraint to the three
columns is correct in my situation. But i want the combination of
those three columns not to allow nulls and they should be unique along
with one primary key to the table
On Oct 8, 9:08 pm, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
> 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:33:32 CDT
