mike 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
>
> So my NUM, ID relationship would look like:
>
> NUM ID
> 1 1
> 1 2
> 1 3
> 2 1
> 2 2
> 2 3
>
> I wanted the ID to be sequential, but can't tie a sequence to it
> because for each NUM I'd like the ID to be 1,2,3, etc. as defined
>
> So, my question then is before I do the insert are there any other ways
> you consider better than doing a query to get the max(id).
>
> My select would be: select max(id) from mytable where num=1
>
> Mike
>
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.
Received on Thu Oct 06 2005 - 15:55:16 CDT
