Marek, please address these general questions to [EMAIL PROTECTED]
----- Original Message ----- From: "Marek Lewczuk" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Friday, November 29, 2002 10:04 AM Subject: Problems with InnoDB > Hello!! > I have read some post about this problem, but there wasn't any good > answers. So after many hours of thinking I've decided to post my > problem to you -> I hope you will help me with this. > > I have to tables tbl1 (primary key `group_id`) and tbl2 (primary key > `element_id`,foreign key `group_id` with on delete contraint). In tbl1 > I have 4 records, in tbl2 I have 3 records. Every record from tbl2 is > connected to correct group_id from tbl1. And my problem is when I need > to change group_id in tbl2 to another (which is also in tbl1) -> when > I want to do this update mysql gives me this error: Cannot delete a > parent row: a foreign key constraint fails. I realy don't know why ?? > I don't want to delete, I just want to update it... I tested this and it worked ok. Are you sure you have defined the foreign key relationship in the right direction? Looks like you have made tbl1 to reference to tbl2, and not the other way around. Your MySQL connection id is 1 to server version: 4.0.6-gamma-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> create table tbl1 (a int not null, primary key (a)) type = innodb; Query OK, 0 rows affected (0.09 sec) mysql> create table tbl2 (b int not null, a int not null, primary key (b), index (a), foreign key (a) references tbl1(a) on delete cascade) type = innodb; Query OK, 0 rows affected (0.07 sec) mysql> insert into tbl1 values (1); Query OK, 1 row affected (0.02 sec) mysql> insert into tbl1 values (2); Query OK, 1 row affected (0.00 sec) mysql> insert into tbl1 values (3); Query OK, 1 row affected (0.00 sec) mysql> insert into tbl1 values (4); Query OK, 1 row affected (0.00 sec) mysql> insert into tbl2 values (1, 1); Query OK, 1 row affected (0.00 sec) mysql> insert into tbl2 values (2, 1); Query OK, 1 row affected (0.00 sec) mysql> insert into tbl2 values (3, 2); Query OK, 1 row affected (0.00 sec) mysql> update tbl2 set a = 3 where b = 1; Query OK, 1 row affected (0.05 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from tbl1; +---+ | a | +---+ | 1 | | 2 | | 3 | | 4 | +---+ 4 rows in set (0.00 sec) mysql> select * from tbl2; +---+---+ | b | a | +---+---+ | 2 | 1 | | 3 | 2 | | 1 | 3 | +---+---+ 3 rows in set (0.01 sec) mysql> ... > I have found the simple solution, before the query I do this: SET > FOREIGN_KEY_CHECKS = 0. But it's not a good solution. Maybe you have > better solution. > > Thanks. > ML Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ See http://www.innodb.com for the online manual and latest news on InnoDB sql query --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
