Re: composite primary keys

On Oct 8, 11:33 am, "g.mrunal..._at_gmail.com" <g.mrunal..._at_gmail.com> wrote:
> 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- Hide quoted text -
>
> - Show quoted text -

Please do not top-post as it disrupts the flow of the thread.

Apparently I cut off the text; the syntax is there, ready for you to use:

SQL>
SQL> alter table mytable
  2 add constraint mytable_uq unique (mynextcol, mynextkey, myothercol);

Table altered.

Additionally the syntax to set a column to NOT NULL:

SQL>
SQL> alter table mytable
  2 modify mynextcol varchar2(20) not null;

Table altered.

SQL>
SQL> alter table mytable
  2 modify mynextkey number not null;

Table altered.

SQL>
SQL> alter table mytable
  2 modify myothercol varchar2(15) not null;

Table altered.

SQL> But you could have figured this out from my original response, I think.

David Fitzjarrell Received on Mon Oct 08 2007 - 12:05:32 CDT