Link to home
Start Free TrialLog in
Avatar of roychan0328
roychan0328

asked on

mysql insert, defer constraints

I have a question regarding deferring foreign key constraints in mySQL.

Suppose I have two tables.... parent and child where child references the parent table.

Suppose I want to insert into both tables in one transaction.  I would run into the problem where when I insert into the child table, it would say, that the parent doesn't exist..and it would violate the foreign key constraint.
Hi Experts,

Normally i would deal with this by something like (as in oracle)...


Set constraint parent_child_fk deferred

insert into parent....
insert into child...

set constraint parent_child_fk immediate
Commit;

How can I achieve this in mySQL ?  I don't want to use SET @@foreign_key_checks = 0 since it disables fk checking completely....and does not check the data that I've inserted after I turn it back on.  

Thank you in advance!
Avatar of miqrogroove
miqrogroove
Flag of United States of America image

To the best of my knowledge there is nothing about transaction handling that would prevent you from inserting two records like this.  You just need to run a third query in between the two INSERTs to obtain the ID of the parent record if you are using an autonumber column as the parent's primary index.
Also please specify which language you are using.  In PHP, for example, there is a function that will return the previous identity without running a third query.
Avatar of roychan0328
roychan0328

ASKER

Currently i am using Java.  But there's no replacement/workaround for deferring foreign key constraint checks?  
What I'm saying is that there is no reason I'm aware of why one would need to defer the key constraint just to insert related records into a MySQL database.  Even if you are sending SQL statements from Java, you can still query the insert identity before sending the child record.
ASKER CERTIFIED SOLUTION
Avatar of miqrogroove
miqrogroove
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
thanks!