Re: composite primary keys

On Oct 8, 12:33 pm, Ed Prochak <edproc..._at_gmail.com> wrote:
> On Oct 8, 1:05 pm, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
>
>
>
>
>
> > 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
>
> David,
> you're spoiling the poor kid. These weren't even Oracle specific
> commands. The syntax for these are straight from standard SQL. How is
> he going to learn to think on his own?
>
> Ed 8^)- Hide quoted text -
>
> - Show quoted text -

Apparently he's incapable of independent action, given his responses to the various posts. Should it come to light that he's merely lazy, then, yes, shame on me. If he's truly unable to construct a coherent thought ...

David Fitzjarrell Received on Mon Oct 08 2007 - 12:43:02 CDT