List:General Discussion« Previous MessageNext Message »
From:Pooly Date:July 22 2006 8:33pm
Subject:Re: transaction
View as plain text  

2006/7/22, João Cândido de Souza Neto <joao@stripped>:
> Ok Dan.
> Thanks a lot for your answer.
> An other doubt about transaction is in the foreign key case. If in a
> transaction i insert a certain register and get the last insert id and after
> i try to insert another register using this id because this table has a
> foreign key, it gives me a contstraint fail for the foreign key, that is, in
> a transaction, the inserts do not get inserted until commit and i can't
> insert registers in tables that has foreign key in the previous table.

You should test it :

mysql> CREATE TABLE t1(id integer auto_increment, field text,primary key(id));

mysql> CREATE TABLE t2(id integer auto_increment, next_id integer,
field text,primary key(id), foreign key (next_id) references t1(id))

mysql> insert into t2 values(1,1,'nope');
ERROR 1452 (23000): Cannot add or update a child row: a foreign key
constraint fails (`test/t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY
(`next_id`) REFERENCES `t1` (`id`))
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1(field) values('my data');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t2(next_id,field) values(last_insert_id(),'my data');
Query OK, 1 row affected (0.07 sec)

mysql> select * from t2;
| id | next_id | field   |
|  1 |       1 | my data |
1 row in set (0.05 sec)

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t2;
Empty set (0.00 sec)

In the table T2 which depends on the table T1, you are able to insert
rows even if you didn't commit your inserts (hopefully :-).

transactionjoao22 Jul
  • Re: transactionDan Nelson22 Jul
  • Re: transactionjoao22 Jul
    • Re: transactionPooly22 Jul