Mark C. Stock wrote:
>>
>>>I have a table defined like:
>>>
>>>CREATE TABLE mytable
>>>(
>>>NUM INTEGER NOT NULL,
>>>ID INTEGER NOT NULL,
>>>TITLE VARCHAR(50) NOT NULL,
>>>.
>>>. (other fields)
>>>.
>>>)
>>>
>>>ALTER TABLE mytable
>>>ADD CONSTRAINT XPK_IDX PRIMARY KEY(NUM, ID)
>>>
>>>ALTER TABLE mytable
>>>ADD CONSTRAINT XFK FOREIGN KEY (NUM)
>>>REFERENCES yourtable(NUM)
>>>ON DELETE CASCADE
>>>
>>Your select will work. If you need to have an order within num column
>>(say line within invoice) then keep things as they are. If mytable.id is
>>not externally meaningful you might make it alone the PK and then you
>>could populate it via a sequence.
>
>
> concurrency is an issue when using MAX(ID), as there is nothing to prevent
> two or more operations from grabbing the same MAX(ID) at the same time and
> attempting to insert using the same value.
>
> in the old days, before oracle supplied SEQUENCE objects, we would record
> the NEXT_ID in a table (usually one column for the TABLE_NAME, one for the
> NEXT_ID, and code a SELECT and an UPDATE against the table. that prevented
> more than one user from attempting to use the same ID, but serialized the
> transactions. which is why we now have SEQUENCE objects.
>
> if there is no business case that requires sequential IDs with no gaps, the
> right way to code SA-PKs in Oracle is with a SEQUENCE.
>
> if there is a business case that requires sequential IDs with no gaps, you
> will either have to live with slower through-put or come up with a scheme
> improve throughput.
>
> AFAIK, this still holds true in 10gR2, but I do know there are some changes
> to the way it handles SEQUENCE objects
>
> ++ mcs
>
Since his ids are dependant upon his nums I suspect his use-cases
probably only have one person updating a yourtable row at a time.
I agree with you assessment about max(id) if it were for the whole table.
Received on Thu Oct 06 2005 - 16:33:54 CDT
