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?