List:General Discussion« Previous MessageNext Message »
From:Heikki Tuuri Date:August 8 2002 6:16am
Subject:Re: Foreign keys vont delete on cascade
View as plain text  
Alexandre,

ON DELETE CASCADE only works starting from 3.23.50 and 4.0.2. The version
numbers are misleading because for the InnoDB subsystem it is the date of
release which determines how new the InnoDB version is. Thus, for InnoDB,
4.0.1 == 3.23.47.

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: ""Alexandre Zglav"" <zglav@stripped>
Newsgroups: mailing.database.mysql
Sent: Thursday, August 08, 2002 6:00 AM
Subject: Foreign keys vont delete on cascade


>
> Hi all ,
>
> Im trying to have innodb to work . I've made my way through the
> configuration process and passed the creation of tables with primarey key
> and foreign keys.
>
> Im am currently trying to do exactly what is done in the innodb manual so
> that i'm sure i'm doing it correct :)
>
> Here is a very simple log of what i've done .
>
> I am working with mysql 4.0.1-alpha-max on a win 2K platform .
>
>
> You will see that ( if every thing is done correctly ) my records in table
'
> enfant ' should be delete when I try to delete a record from table '
parent'
> , where the id of table parent is referenced in the enfant ' table ' ....
> errrm well never mind lets get to that log .
>
> I hope you can help me . Its been a long time since ive been trying to get
> this stuff to work and I'd really appreciate a tip or two :)
>
> See ya all
>
>
>
> Wmysql> CREATE DATABASE famille
>     -> ;
> Query OK, 1 row affected (0.01 sec)
>
> mysql> CREATE TABLE parent (id INT NOT NULL, nom CHAR(10), PRIMARY KEY
(id))
> TYPE=INNODB;
> ERROR 1046: No Database Selected
> mysql> use famille
> Database changed
> mysql> CREATE TABLE parent (id INT NOT NULL, nom CHAR(10), PRIMARY KEY
(id))
> TYPE=INNODB;
> Query OK, 0 rows affected (0.09 sec)
>
> mysql> CREATE TABLE enfant (id INT, parent_id INT, INDEX par_id
(parent_id),
> FOREIGN KEY (parent_id) REFERENCES parent(id) ON
>  DELETE CASCADE) TYPE=INNODB;
> Query OK, 0 rows affected (0.70 sec)
>
> mysql> INSERT INTO parent (id , nom) VALUES (1, 'alex');
> Query OK, 1 row affected (0.04 sec)
>
> mysql> INSERT INTO parent (id , nom) VALUES (3, 'tonio');
> Query OK, 1 row affected (0.02 sec)
>
> mysql> INSERT INTO parent (id , nom) VALUES (24, 'poulaga');
> Query OK, 1 row affected (0.02 sec)
>
> mysql> INSERT INTO parent (id , nom) VALUES (22, 'hellscream');
> Query OK, 1 row affected (0.02 sec)
>
> mysql> select * from parent;
> +----+------------+
> | id | nom        |
> +----+------------+
> |  1 | alex       |
> |  3 | tonio      |
> | 22 | hellscream |
> | 24 | poulaga    |
> +----+------------+
> 4 rows in set (0.00 sec)
>
> mysql> insert into enfant ( id, parent_id) VALUES (12, 223):
>     -> ;
> ERROR 1064: You have an error in your SQL syntax near ':' at line 1
> mysql> insert into enfant ( id, parent_id) VALUES (12, 223);
> ERROR 1216: Cannot add a child row: a foreign key constraint fails
> mysql> insert into enfant ( id, parent_id) VALUES (12, 3);
> Query OK, 1 row affected (0.04 sec)
>
> mysql> YEEHAA;
>
> ERROR 1064: You have an error in your SQL syntax near 'YEEHAA' at line 1
>
> DOH !
>
> mysql> select * from enfant;
> +------+-----------+
> | id   | parent_id |
> +------+-----------+
> |   12 |         3 |
> +------+-----------+
> 1 row in set (0.00 sec)
>
>
> mysql> insert into enfant ( id, parent_id) VALUES (4, 3);
> Query OK, 1 row affected (0.02 sec)
>
> mysql> insert into enfant ( id, parent_id) VALUES (4, 22);
> Query OK, 1 row affected (0.02 sec)
>
> mysql> select * from enfant;
> +------+-----------+
> | id   | parent_id |
> +------+-----------+
> |   12 |         3 |
> |   14 |         3 |
> |    4 |         3 |
> |    4 |         3 |
> |    4 |        22 |
> +------+-----------+
> 5 rows in set (0.00 sec)
>
> mysql> DELETE FROM parent WHERE ID = 3;
> ERROR 1217: Cannot delete a parent row: a foreign key constraint fails
>
> !!!
>
>
> mysql> DELETE FROM parent WHERE nom = tonio;
> ERROR 1054: Unknown column 'tonio' in 'where clause'
> mysql> DELETE FROM parent WHERE nom = 'tonio';
> ERROR 1217: Cannot delete a parent row: a foreign key constraint fails
> mysql> DELETE FROM parent WHERE nom = 'tonio';
> ERROR 1217: Cannot delete a parent row: a foreign key constraint fails
> mysql> DROP TABLE parent;
> Query OK, 0 rows affected (0.27 sec)
>
> mysql> DROP TABLE enfant
>     -> ;
> Query OK, 0 rows affected (0.11 sec)
>
> mysql> CREATE TABLE parent (id INT NOT NULL AUTO_INCREMENT, nom CHAR(10),
> PRIMARY KEY (id)) TYPE=INNODB;
> Query OK, 0 rows affected (0.20 sec)
>
> mysql> CREATE TABLE enfant (id INT, parent_id INT, INDEX par_id
(parent_id),
> FOREIGN KEY (parent_id) REFERENCES parent(id) ON
>  DELETE CASCADE) TYPE=INNODB;
> Query OK, 0 rows affected (0.08 sec)
>
> mysql> INSERT INTO parent (id , nom) VALUES (1, 'alex');
> Query OK, 1 row affected (0.03 sec)
>
> mysql> INSERT INTO parent (nom) VALUES ('alex2');
> Query OK, 1 row affected (0.03 sec)
>
> mysql> INSERT INTO parent (nom) VALUES ('alex3');
> Query OK, 1 row affected (0.02 sec)
>
> mysql> INSERT INTO parent (nom) VALUES ('alex4');
> Query OK, 1 row affected (0.03 sec)
>
> mysql> INSERT INTO parent (nom) VALUES ('alex5');
> Query OK, 1 row affected (0.05 sec)
>
> mysql> SELECT * FROM parent
>     -> ;
> +----+-------+
> | id | nom   |
> +----+-------+
> |  1 | alex  |
> |  2 | alex2 |
> |  3 | alex3 |
> |  4 | alex4 |
> |  5 | alex5 |
> +----+-------+
> 5 rows in set (0.01 sec)
>
> mysql> INSERT INTO ENFANT (id, parent_ID) VALUES (1,5);
> Query OK, 1 row affected (0.03 sec)
>
> mysql> INSERT INTO ENFANT (id, parent_ID) VALUES (1,6);
> ERROR 1216: Cannot add a child row: a foreign key constraint fails
> mysql> INSERT INTO ENFANT (id, parent_ID) VALUES (1,3);
> Query OK, 1 row affected (0.02 sec)
>
> mysql> INSERT INTO ENFANT (id, parent_ID) VALUES (4,6);
> ERROR 1216: Cannot add a child row: a foreign key constraint fails
> mysql> INSERT INTO ENFANT (id, parent_ID) VALUES (4,2);
> Query OK, 1 row affected (0.03 sec)
>
> mysql> INSERT INTO ENFANT (id, parent_ID) VALUES (6,2);
> Query OK, 1 row affected (0.03 sec)
>
> mysql> INSERT INTO ENFANT (id, parent_ID) VALUES (7,2);
> Query OK, 1 row affected (0.04 sec)
>
> mysql> INSERT INTO ENFANT (id, parent_ID) VALUES (456,2);
> Query OK, 1 row affected (0.03 sec)
>
> mysql> SELECT * FROM parent;
> +----+-------+
> | id | nom   |
> +----+-------+
> |  1 | alex  |
> |  2 | alex2 |
> |  3 | alex3 |
> |  4 | alex4 |
> |  5 | alex5 |
> +----+-------+
> 5 rows in set (0.00 sec)
>
> mysql> SELECT * FROM enfant;
> +------+-----------+
> | id   | parent_id |
> +------+-----------+
> |    1 |         5 |
> |    1 |         3 |
> |    4 |         2 |
> |    6 |         2 |
> |    7 |         2 |
> |  456 |         2 |
> +------+-----------+
> 6 rows in set (0.00 sec)
>
> mysql> DELETE FROM ENFANT WHERE id = 2
>     -> ;
> Query OK, 0 rows affected (0.00 sec)
>
> mysql> SELECT * FROM enfant;
> +------+-----------+
> | id   | parent_id |
> +------+-----------+
> |    1 |         5 |
> |    1 |         3 |
> |    4 |         2 |
> |    6 |         2 |
> |    7 |         2 |
> |  456 |         2 |
> +------+-----------+
> 6 rows in set (0.00 sec)
>
> mysql> DELETE FROM parent WHERE id = 2;
> ERROR 1217: Cannot delete a parent row: a foreign key constraint fails
>
> Well it wont work at all as you can see .
>
> What am I getting wrong ?
>
> mysql> DELETE FROM enfant WHERE ID = 7;
> Query OK, 1 row affected (0.02 sec)
>
> mysql> SELECT * FROM enfant;
> +------+-----------+
> | id   | parent_id |
> +------+-----------+
> |    1 |         5 |
> |    1 |         3 |
> |    4 |         2 |
> |    6 |         2 |
> |  456 |         2 |
> +------+-----------+
> 5 rows in set (0.00 sec)
>
> mysql> SELECT * FROM parent;
> +----+-------+
> | id | nom   |
> +----+-------+
> |  1 | alex  |
> |  2 | alex2 |
> |  3 | alex3 |
> |  4 | alex4 |
> |  5 | alex5 |
> +----+-------+
> 5 rows in set (0.00 sec)
>
> mysql>
> ---
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.375 / Virus Database: 210 - Release Date: 10.07.2002
>
>
> ---------------------------------------------------------------------
> 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 <mysql-thread116640@stripped>
> To unsubscribe, e-mail
<mysql-unsubscribe-mysql=freebsd.csie.nctu.edu.tw@stripped>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>


Thread
Foreign keys in query optimizationKiss D├íniel10 Jun
  • Re: Foreign keys in query optimizationharm de laat10 Jun
  • Re: Foreign keys in query optimizationcal10 Jun
  • Re: Foreign keys in query optimizationVictoria Reznichenko10 Jun
    • Foreign keys vont delete on cascadeAlexandre Zglav8 Aug
RE: Foreign keys in query optimizationWalt Weaver10 Jun
Re: Foreign keys vont delete on cascadeHeikki Tuuri8 Aug
  • RE: Foreign keys vont delete on cascadeAlexandre Zglav9 Aug
    • RE: Foreign keys vont delete on cascadeAlexandre Zglav9 Aug