Foreign key contraints, on delete cascade not working?

[Andrew DeFaria]

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?

Heikki Tuuri asked me to look at my innodb variables and I found:

mysql> show variables like "%innodb%";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| have_innodb   | NO    |
+---------------+-------+
1 row in set (0.00 sec)

But I still must ask: Why is that? How do I turn it on?

(For the record, when I was prototyping this db I tested this and it worked like a champ. Sometime later it stopped working).

Hmmm... Interesting:

mysql> show variables like "%innodb%";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| have_innodb   | NO    |
+---------------+-------+
1 row in set (0.05 sec)

mysql> set variable have_innodb=yes;
ERROR 1193: Unknown system variable 'variable'
mysql> set have_innodb=yes;
ERROR 1193: Unknown system variable 'have_innodb'


--
Think "honk" if you're telepathic.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]