Andrew,
you should send your MySQL questions to
my***@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" <An****@DeFaria.com>
Newsgroups: mailing.database.mysql
Sent: Saturday, January 10, 2004 7:03 AM
Subject: foreign key contraints, on delete cascade not working?
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.
"Andrew DeFaria" <An****@DeFaria.com> kirjoitti viestissä
news:1d***************************@msgid.meganewss ervers.com... 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.
