CODEWITHSUNDEEP
mysqlauto-increment
james_bond
james_bond

Reputation: 6908

Autoincrement problem mysql

I've looked for this but no luck so far. I have two tables in mysql, they are configured as parent-child. The parent table has a field of type auto_increment, and the child has a field that references the autoincrement column in the parent. When I do inserts into the parent table everything goes fine. But when I do insert on the child table it gives me an exception that says there are no reference column value in the parent table. The question is, how do I refresh the value of the autoincrement id AFTER THE INSERT so the child has a reference to that BEFORE executing its UPDATE (or insert in this case).

Upvotes: 0

Views: 963

Answers (3)

Vince Bowdren
Vince Bowdren

Reputation: 9198

It sounds like you want to insert a new record to the parent table at the same time as you're inserting the new record in the child table. Try doing it with a stored procedure? That way, you can do the insert into the parent table, store the identifier in a variable, then save that variable into the foreign key field in the new record in the child table.

Upvotes: 0

bkilinc
bkilinc

Reputation: 989

on inserting a new record always insert to parent first. Get the auto_increment value (use LAST_INSERT_ID() in mysql) , and use this in child table. On the very start you can start a transcation, so if something goes wrong on inserting to child, you can roll back.

Upvotes: 1

sleske
sleske

Reputation: 83577

Your question is a bit unclear, but I assume you have a foreign key constraint on a column in the child table that references the auto_increment field in the parent table, correct?

In that case you need to set a valid value in the FK column of the child table for every update or insert on the child table. Do you still get an error if you do this?

The question is, how do I refresh the value of the autoincrement id AFTER THE INSERT so the child has a reference to that BEFORE executing its UPDATE (or insert in this case).

This does not make sense to me. What do you mean by refresh the value of the autoincrement id?

Normally, after inserting into the parent table, you retrieve the generated auto_increment value (how to do this depends on the API you use, but all database APIs can do this), set this value to the appropriate column in the child table, then insert/update the child table.

Upvotes: 0

Related Questions