I think the best way would be not to use a SERIAL field, but an INTEGER
field and a sequence:
CREATE SEQUENCE parent_seq;
CREATE TABLE parent(id INTEGER, descrip CHAR(50));
So when you want to insert on the parent table, you obtain the next
value from the sequence and then you insert in the parent and child
tables the value you obtained:
newId:=SELECT nextval('parent_seq')
INSERT INTO parent(id, descrip) VALUES (newId, 'XXXX');
INSERT INTO child_1(..., ..., parentId) VALUES (..., ..., newId);
INSERT INTO child_2(..., ..., parentId) VALUES (..., ..., newId);
INSERT INTO child_3(..., ..., parentId) VALUES (..., ..., newId);
hope it helps.
MaRCeLO PeReiRA wrote:
>Hi guys,
>
>I am in troubles with a SERIAL field.
>
>I have five tables. A parent table and four child
>tables. When I do the INSERT in the parent table, I
>have an ID (generated) by the sequence (SERIAL field),
>and I have to use this ID to reference all child
>tables.
>
>Well, once I do an INSERT in the parent table, how can
>I know (for sure) which number id was generated by the
>sequence?
>
>Simple example:
>
>------------------------------------------------------
>CREATE TABLE parent(id SERIAL, descrip CHAR(50));
>------------------------------------------------------
>
>So,
>
>------------------------------------------------------
>INSERT INTO parent (descrip) VALUES ('project 1');
>------------------------------------------------------
>
>How can I now (for sure) with value was generated by
>the sequence to fill the field ID?
>
>(There is lots of users using the software at the same
>time, so I am not able to use the last_value()
>function on the sequence.)
>
>Best Regards,
>
>Marcelo Pereira
>Brazil
>
>
>
>
>
>_______________________________________________________
>Yahoo! Acesso Grátis - Internet rápida e grátis. Instale o discador agora! http://br.acesso.yahoo.com/
>
>---------------------------(end of broadcast)---------------------------
>TIP 4: Don't 'kill -9' the postmaster
>
>
>