Re: innodb on delete cascade

[R.Dobson]

Hi, i'm using Distrib 4.0.1-alpha, for sun-solaris2.8 (sparc)

Victoria Reznichenko wrote:

"R.Dobson" <[EMAIL PROTECTED]> wrote:
 

mmm, i've just tried the example within the mysql docs:

CREATE TABLE parent(id INT NOT NULL, PRIMARY KEY (id)) TYPE=INNODB;
CREATE TABLE child(id INT, parent_id INT, INDEX par_ind (parent_id),
           FOREIGN KEY (parent_id) REFERENCES parent(id)
           ON DELETE cascade
) TYPE=INNODB;
Now, insert a couple of lies of data:

mysql> insert into parent values(1);
Query OK, 1 row affected (0.00 sec)
mysql> insert into parent values(2);
Query OK, 1 row affected (0.00 sec)
mysql> insert into parent values(3);
Query OK, 1 row affected (0.00 sec)
mysql> insert into child values(1,1);
Query OK, 1 row affected (0.01 sec)
mysql> insert into child values(2,2);
Query OK, 1 row affected (0.00 sec)
mysql> insert into child values(3,3);
Query OK, 1 row affected (0.00 sec)
mysql> select * from child;
+------+-----------+
| id   | parent_id |
+------+-----------+
|    1 |         1 |
|    2 |         2 |
|    3 |         3 |
+------+-----------+
3 rows in set (0.00 sec)
mysql> select * from parent;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
+----+
3 rows in set (0.00 sec)


When I come to try to delete some data from the parent table i'm getting 
errors as in:

mysql> delete from parent where id=1;
ERROR 1217: Cannot delete a parent row: a foreign key constraint fails
any thoughts?

   

Worked perfect for me:

mysql> delete from parent where id=1;
Query OK, 1 row affected (0.04 sec)
mysql> select * from child;
+------+-----------+
| id   | parent_id |
+------+-----------+
|    2 |         2 |
|    3 |         3 |
+------+-----------+
2 rows in set (0.02 sec)
mysql> select * from parent;
+----+
| id |
+----+
|  2 |
|  3 |
+----+
2 rows in set (0.00 sec)
What version of MySQL do you use?