MySQL Forums :: Connector/Python :: mysql transactions - foreign key failure


Advanced Search

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?

thanks
Aaron

Options: ReplyQuote


Subject Written By Posted
mysql transactions - foreign key failure Aaron Peachey 09/27/2009 05:40AM
Re: mysql transactions - foreign key failure (update: actually LAST_INSERT_ID() issue) Aaron Peachey 09/27/2009 06:40AM
Re: mysql transactions - foreign key failure (update: actually LAST_INSERT_ID() issue) Geert Vanderkelen 09/28/2009 06:03AM
Re: mysql transactions - foreign key failure (update: actually LAST_INSERT_ID() issue) Aaron Peachey 10/01/2009 05:51AM
Re: mysql transactions - foreign key failure Geert Vanderkelen 09/28/2009 05:59AM


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.