Hi!
Please check that you are using >= 3.23.50.
I tested this on Win NT-4.0 with 3.23.52, and it worked:
mysql> CREATE TABLE parent(id INT NOT NULL,
-> PRIMARY KEY (id)) TYPE=INNODB;
Query OK, 0 rows affected (0.14 sec)
mysql> 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;
Query OK, 0 rows affected (0.04 sec)
mysql>
mysql> insert into parent values(5);
Query OK, 1 row affected (0.00 sec)
mysql> insert into child values(0,5);
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> delete from parent where id=5;
Query OK, 1 row affected (0.01 sec)
mysql>
mysql>
mysql> select * from parent;
Empty set (0.00 sec)
mysql> select * from child;
Empty set (0.01 sec)
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
----- Original Message -----
From: ""Me&"" <[EMAIL PROTECTED]>
Newsgroups: mailing.database.mysql
Sent: Friday, May 31, 2002 11:36 AM
Subject: InnoDB Delete On Cascade
> Hello People,
>
> I was just designing my database and I was planning to finally use the new
> feature provided by InnoDB : ON DELETE CASCADE
>
>
> So I tried the example :
>
> 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;
>
> Created two records :
> insert into parent values(5);
> insert into child values(0,5);
>
> Ok this works all fine. Also geives me errors when I give it a parent ID
of
> an unexisting record, so works like it should.
>
> Now isn't the goal of ON DELETE CASCADE is that when the parent record is
> deleted that the child records that reference the parent record id get
> deleted aswell?
>
> Because this doesn't seem to work. I get :
> mysql> delete from parent where id=5;
> ERROR 1217: Cannot delete a parent row: a foreign key constraint fails
>
> Do I have the wrong idea baout the feature or what might be wrong?
>
> Using Version 3.23.50-max-nt and InnoDB.
>
>
> ---------------------------------------------------------------------
> 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
>
---------------------------------------------------------------------
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
