Hi,
2006/7/22, João Cândido de Souza Neto <[EMAIL PROTECTED]>:
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))
ENGINE=InnoDB;
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 :-).
HIMH
--
http://www.w-fenec.org/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
