Andrew,
you should send your MySQL questions to
mysql@lists.mysql.com. This
newsgroup is not as active as that list.
Please check with
SHOW VARIABLES LIKE '%innodb%';
that you really have InnoDB enabled.
I tested the script below on Linux with 4.0.8 and 4.0.18, and it worked ok.
Best regards,
Heikki Tuuri
Innobase Oy
http://www.innodb.com
Foreign keys, transactions, and row level locking for MySQL
InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM
tables
Order MySQL technical support from
https://order.mysql.com/
heikki@hundin:~/mysql-4.0/client> mysql test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.0.18-debug
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> drop database if exists MYDB;
Query OK, 0 rows affected (0.06 sec)
mysql> create database MYDB;
Query OK, 1 row affected (0.00 sec)
mysql> use MYDB;
Database changed
mysql> create table user (
-> userid varchar (8) not null,
-> name tinytext not null,
-> primary key (userid)
-> ) type=innodb; -- user
Query OK, 0 rows affected (0.03 sec)
mysql> create table useropts (
-> userid varchar (8) not null,
-> name tinytext,
-> value varchar (128),
-> key user_index (userid),
-> foreign key (userid) references user (userid) on delete cascade
-> ) type=innodb; -- useropts
Query OK, 0 rows affected (0.01 sec)
mysql> insert into user values ("userA", "User A");
Query OK, 1 row affected (0.00 sec)
mysql> insert into useropts values ("userA", "option", "value");
Query OK, 1 row affected (0.00 sec)
mysql> select * from user;
+--------+--------+
| userid | name |
+--------+--------+
| userA | User A |
+--------+--------+
1 row in set (0.00 sec)
mysql> select * from useropts;
+--------+--------+-------+
| userid | name | value |
+--------+--------+-------+
| userA | option | value |
+--------+--------+-------+
1 row in set (0.00 sec)
mysql> delete from user;
Query OK, 1 row affected (0.00 sec)
mysql> select * from useropts;
Empty set (0.00 sec)
mysql> select * from user;
Empty set (0.01 sec)
mysql>
----- Original Message -----
From: "Andrew DeFaria" <Andrew@DeFaria.com>
Newsgroups: mailing.database.mysql
Sent: Saturday, January 10, 2004 7:03 AM
Subject: foreign key contraints, on delete cascade not working?
[color=blue]
> I created the following .sql file to demonstrate a problem I'm having.
> According to the manual:
>
> If |ON DELETE CASCADE| is specified, and a row in the parent table
> is deleted, then InnoDB automatically deletes also all those rows in
> the child table whose foreign key values are equal to the referenced
> key value in the parent row.
>
> However:
>
> drop database if exists MYDB;
> create database MYDB;
> use MYDB;
> create table user (
> userid varchar (8) not null,
> name tinytext not null,
> primary key (userid)
> ) type=innodb; -- user
> create table useropts (
> userid varchar (8) not null,
> name tinytext,
> value varchar (128),
> key user_index (userid),
> foreign key (userid) references user (userid) on delete cascade
> ) type=innodb; -- useropts
> insert into user values ("userA", "User A");
> insert into useropts values ("userA", "option", "value");
> select * from user;
> select * from useropts;
> delete from user;
> select * from useropts;
> select * from user;
>
> $ mysql
> Welcome to the MySQL monitor. Commands end with ; or \g.
> Your MySQL connection id is 35215 to server version: 4.0.10-gamma
>
> Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
>
> mysql> source MYDB.sql
> Query OK, 0 rows affected (0.01 sec)
>
> Query OK, 1 row affected (0.00 sec)
>
> Database changed
> Query OK, 0 rows affected (0.00 sec)
>
> Query OK, 0 rows affected (0.00 sec)
>
> Query OK, 1 row affected (0.00 sec)
>
> Query OK, 1 row affected (0.01 sec)
>
> +--------+--------+
> | userid | name |
> +--------+--------+
> | userA | User A |
> +--------+--------+
> 1 row in set (0.00 sec)
>
> +--------+--------+-------+
> | userid | name | value |
> +--------+--------+-------+
> | userA | option | value |
> +--------+--------+-------+
> 1 row in set (0.00 sec)
>
> Query OK, 1 row affected (0.00 sec)
>
> +--------+--------+-------+
> | userid | name | value |
> +--------+--------+-------+
> | userA | option | value |
> +--------+--------+-------+
> 1 row in set (0.00 sec)
>
> Empty set (0.00 sec)
>
> As you can see I when I delete from user (the parent table) the useropts
> (child table) entry remains. Shouldn't it be deleted?
>
>
> --
> E-mail returned to sender -- insufficient voltage.
>[/color]
"Andrew DeFaria" <Andrew@DeFaria.com> kirjoitti viestissä
news:1d3f4$3fff87bf$44a7886c$12806@msgid.meganewss ervers.com...[color=blue]
> I created the following .sql file to demonstrate a problem I'm having.
> According to the manual:
>
> If |ON DELETE CASCADE| is specified, and a row in the parent table
> is deleted, then InnoDB automatically deletes also all those rows in
> the child table whose foreign key values are equal to the referenced
> key value in the parent row.
>
> However:
>
> drop database if exists MYDB;
> create database MYDB;
> use MYDB;
> create table user (
> userid varchar (8) not null,
> name tinytext not null,
> primary key (userid)
> ) type=innodb; -- user
> create table useropts (
> userid varchar (8) not null,
> name tinytext,
> value varchar (128),
> key user_index (userid),
> foreign key (userid) references user (userid) on delete cascade
> ) type=innodb; -- useropts
> insert into user values ("userA", "User A");
> insert into useropts values ("userA", "option", "value");
> select * from user;
> select * from useropts;
> delete from user;
> select * from useropts;
> select * from user;
>
> $ mysql
> Welcome to the MySQL monitor. Commands end with ; or \g.
> Your MySQL connection id is 35215 to server version: 4.0.10-gamma
>
> Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
>
> mysql> source MYDB.sql
> Query OK, 0 rows affected (0.01 sec)
>
> Query OK, 1 row affected (0.00 sec)
>
> Database changed
> Query OK, 0 rows affected (0.00 sec)
>
> Query OK, 0 rows affected (0.00 sec)
>
> Query OK, 1 row affected (0.00 sec)
>
> Query OK, 1 row affected (0.01 sec)
>
> +--------+--------+
> | userid | name |
> +--------+--------+
> | userA | User A |
> +--------+--------+
> 1 row in set (0.00 sec)
>
> +--------+--------+-------+
> | userid | name | value |
> +--------+--------+-------+
> | userA | option | value |
> +--------+--------+-------+
> 1 row in set (0.00 sec)
>
> Query OK, 1 row affected (0.00 sec)
>
> +--------+--------+-------+
> | userid | name | value |
> +--------+--------+-------+
> | userA | option | value |
> +--------+--------+-------+
> 1 row in set (0.00 sec)
>
> Empty set (0.00 sec)
>
> As you can see I when I delete from user (the parent table) the useropts
> (child table) entry remains. Shouldn't it be deleted?
>
>
> --
> E-mail returned to sender -- insufficient voltage.
>[/color]
