Home » SQL & PL/SQL » SQL & PL/SQL » How to use a sequence to populate a composite PK key
How to use a sequence to populate a composite PK key [message #218976] Mon, 12 February 2007 05:58 Go to next message
bahy91
Messages: 91
Registered: September 2005
Location: Amsterdam
Member
Hi everyone,
can anyone show me by an example how to create a sequence that will populate composite primary key..?


thanks in advance,
bahaa
Re: How to use a sequence to populate a composite PK key [message #218979 is a reply to message #218976] Mon, 12 February 2007 06:11 Go to previous messageGo to next message
gintsp
Messages: 118
Registered: February 2007
Senior Member
Composite primary keys usually are used only if one is using natural keys as primary keys. As a result using natural keys there is no need for sequences, because part of business data are used for PK. If you are using surrogates then composite primary keys is really a bad idea, because then you are using the weaknesses both from natural keys (composite key, especially when used for FKs) and surrogates (pure overhead, more data, more indexes).

So the real question is - why do you think you need sequences for composite PKs?

Gints Plivna
http://www.gplivna.eu
Re: How to use a sequence to populate a composite PK key [message #218981 is a reply to message #218979] Mon, 12 February 2007 06:25 Go to previous messageGo to next message
bahy91
Messages: 91
Registered: September 2005
Location: Amsterdam
Member
Thank you for your replay,
we have a table used to keep the records of usergroup+contents+Accesstypes ..these three columns needs to be unique for the business rules that we have.
i create a composite PK for these three columns but i couldn't find the way to build a sequence that will use increment numbering for this table.

ALTER TABLE test ADD CONSTRAINT PK_test PRIMARY KEY (nrUserGroup,nrContents,AccessType)
USING INDEX TABLESPACE &TS_INDEXES;


thanks in advance,
bahaa
Re: How to use a sequence to populate a composite PK key [message #218982 is a reply to message #218981] Mon, 12 February 2007 06:31 Go to previous messageGo to next message
gintsp
Messages: 118
Registered: February 2007
Senior Member
Hmmm it is not clear what are exact purposes for these columns as well as table as such, but aren't these columns just FKs to other tables? If it is so, then you absolutely cannot use sequence, you have to get the right value from parent table and insert this one. Otherwise please describe the business what are this table for.

Gints Plivna
http://www.gplivna.eu
Re: How to use a sequence to populate a composite PK key [message #218987 is a reply to message #218982] Mon, 12 February 2007 06:57 Go to previous messageGo to next message
bahy91
Messages: 91
Registered: September 2005
Location: Amsterdam
Member
Thank you for your replay,


but aren't these columns just FKs to other tables? If it is so, then you absolutely cannot use sequence, you have to get the right value from parent table and insert this one.

yes they are all a FKs from other tables..so what is the proper way to create a sequence for this table...


thanks in advance,
bahaa
Re: How to use a sequence to populate a composite PK key [message #218992 is a reply to message #218987] Mon, 12 February 2007 07:14 Go to previous messageGo to next message
gintsp
Messages: 118
Registered: February 2007
Senior Member
Generally for this table (test) you don't use sequences then. You use sequences to populate the parent tables.

So the most ordinary scenario probably is - you have populated usergroups, contents, accesstypes tables with some data. You find out a row from each of the tables and then simply use
INSERT INTO test VALUES (NecessaryIDFromUsergroups, NecessaryIDFromContents, NecessaryIDFromAccestypes).

The another scenario probably is if you want at once insert both parent rows and one child row into test table.
Then you use following inserts to populate parent tables:
INSERT INTO usergroups VALUES (usg_seq.nextval, OtherColumnData);
INSERT INTO contents VALUES (cnt_seq.nextval, OtherColumnData);
INSERT INTO accesstypes VALUES (act_seq.nextval, OtherColumnData);
And following insert to populate child table:
INSERT INTO test VALUES (usg_seq.currval, cnt_seq.currval, act_seq.currval);

Of course if some of your parent data already exists then you just pick up necessary ID.

Currval can be used only if you have already incremented particular sequence in the current session.

Another way of getting nextval value back to calling program is to use RETURNING clause of INSERT statement. About that you can read in Oracle docs at tahiti.oracle.com, pick your version and then look into SQL reference guide.

Gints Plivna
http://www.gplivna.eu
Re: How to use a sequence to populate a composite PK key [message #218995 is a reply to message #218992] Mon, 12 February 2007 07:40 Go to previous message
bahy91
Messages: 91
Registered: September 2005
Location: Amsterdam
Member
Thank you very much ..this is all what i need.


thanks in advance,
bahaa
Previous Topic: Upgrading jdk from 1.4.2 to 1.5 for oracle client.
Next Topic: instr function
Goto Forum:
  


Current Time: Sun Dec 04 18:25:39 CST 2016

Total time taken to generate the page: 0.22548 seconds