Posted by Mary Walker on 11/05/05 19:45

Hi,

I'm enclosed a snippet of test code which highlights my problem. The Stored
procedure insertValue should insert text into the parent, then insert other
text into the child table but the 2 tables should auto increment in sync
(i.e. so that they both end up with the same id numbers). I've tried taking
the auto increment out of the child table but then I don't know how to get
the right parent id into the child table.

Any advice appreciated - this is my first database, so I'm just in the
learning process really. Code follows:

CREATE TABLE Parent
(id INTEGER DEFAULT AUTOINCREMENT,
parenttext VARCHAR(16),
PRIMARY KEY (id))!

CREATE TABLE Child
(childID INTEGER INTEGER DEFAULT AUTOINCREMENT,
childtext VARCHAR(16),
FOREIGN KEY (childid) REFERENCES Parent(id),
PRIMARY KEY (childID))!

CREATE PROCEDURE insertValues(in p VARCHAR(16), in c VARCHAR(16))
BEGIN
insert into parent (parenttext) values (p);
insert into child (childtext) values (c);
END!

call insertValues('from parent', 'from child')!
select * from parent, child where parent.id = child.childid!

[Back to original message]