Andrew, 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/ [EMAIL PROTECTED]:~/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" <[EMAIL PROTECTED]> 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. > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
