MySQL Forums
Forum List  »  Connector/Python

mysql transactions - foreign key failure
Posted by: Aaron Peachey
Date: September 27, 2009 05:40AM

Hi all,

I have a couple of tables in MySQL. The id in table 1 (parent) is an auto_increment value and this is a foreign key in table 2 (child).

I'm writing a python program which starts a transaction. First it tries to insert a new record into the parent table.
It then uses:

cur.execute("select LAST_INSERT_ID() from parent")

to get the auto increment id.
This id is then used as a foreign key to insert a row into the child table before committing the transaction.

The second insert keeps throwing an error, saying the foreign key constraint has been broken.

My understanding of transactions (could be wrong) is that whilst they are not available to other connections until the transaction is committed, the statements inside the transaction should be able to use the results of the earlier statements as if they had already been written to the database.
Is this not the case?

If not, how would I handle the situation of using the auto increment id generated in the first insert to insert the second row? The kicker here is that i want to be able to roll back the first insert if the second one fails - that's why I am using transactions.

Any ideas?


Options: ReplyQuote

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.