Autoincrement problem mysql

Question

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).

Answer 1

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.

Answer 2

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.