Re: Create Sequence on Dual Primary Key

From: Michael Kuznetsov <mvk_at_servocomp.ru>
Date: 14 Jun 2003 05:41:55 -0700
Message-ID: <1543a3e7.0306140441.53d5ec10_at_posting.google.com>


Michael Hill <hillmw_at_ram.lmtas.lmco.com> wrote in message news:<3EEA2DA4.56400B61_at_ram.lmtas.lmco.com>...
> If I have a table like:
> create table my_table
> (
> spec_id,
> spec_num,
> ....<snip - other fields>
> )
> and then create a primary key like:
>
> alter table task add primary key (spec_id, spec_num)
>
> How do i build a seqence?
>
> spec_id is a foreign key to another table
> spec_num should be a sequence number within spec_id
>
> examples of spec_id, spec_num combinations:
>
> 131, 1
> 131, 2
> 131, 3
> 132, 1
>
> Mike

Hi Mike,

In case like yours, I usually create primary key only for field spec_num and use for its generation simple sequence. For field spec_id I create index. It's clear, fast and enough for most queries (For example can enumerate links to the same spec_id dynamically in select).

If you really need remember order of creating links to the same spec_id (I don't see other reason to make spec_num as you wish) it is not simple task. You should do it in your program. For example you should select database for current maximum spec_num for spec_id, add one and use this value for next spec_num. And don't remember that it can do some concurrent sessions. You should provide appropriate locking or be ready to handle duplicate primary key exception.

Regards,
Michael
Brainbench MVP for Oracle Programming
http://www.brainbench.com Received on Sat Jun 14 2003 - 14:41:55 CEST

Original text of this message