Re: Create Sequence on Dual Primary Key

Michael Hill wrote:
>
> 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

I'd strongly reccomend against doing it this way unless you are absoultely sure that you will never ever need to update a record to point to a new spec_id. Especially if you are going to use this primary key as a parent for a foreign key.

I inherited some tables that were designed this way and it was a genuine PITA to update the spec_id for a record. You had to figure out the lowest unused spec_num for the new spec_id, then update the spec_num and spec_id. If you had dependent tables, you had to update them too, which meant either turning off the referential integrity for the duration of the update or creating a temporary dummy record. Yuck.

Compared the above mess to the simple "UPDATE task SET spec_id = 'new_value' WHERE spec_num = '_VALUE_'.

My advice: just create a sequence for spec_num, and make spec_num the PK for the table. If you need a first, second, third, etc you can do that by including an insert_timestamp and calculate it on SELECT.

-- 
//-Walt
// 
//