Re: mysql transactions - foreign key failure

[Geert Vanderkelen]

Hey Aaron,

I made a small example, showing transaction and foreign key.
Note:
1) Using InnoDB storage engine
2) Explicit commit

Hope this helps.

-Geert

table1 = """CREATE TABLE t1 (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
c1 INT,
PRIMARY KEY (id)
) ENGINE=InnoDB
"""
table2 = """CREATE TABLE t2 (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
fid INT UNSIGNED NOT NULL,
PRIMARY KEY (id),
INDEX (fid),
FOREIGN KEY (fid) REFERENCES t1(id)
) ENGINE=InnoDB"""
cursor = db.cursor()

cursor.execute("DROP TABLE IF EXISTS t2, t1")
cursor.execute(table1)
cursor.execute(table2)

stmt = "INSERT INTO t1 (c1) VALUES (%s)"
cursor.execute(stmt,(599,))
stmt = "SELECT LAST_INSERT_ID()"
cursor.execute(stmt)
lastId = cursor.fetchone()[0]
stmt = "INSERT INTO t2 (fid) VALUES (%s)"
cursor.execute(stmt,(lastId,))
db.commit()

cursor.execute("SELECT t1.*, t2.* FROM t1,t2 WHERE t2.fid = t1.id")

print cursor.fetchall()

cursor.close()

Geert Vanderkelen
Software Developer at Oracle