Andrew DeFaria <[EMAIL PROTECTED]> wrote:
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?
Do you use 3.23.xx version?
I'm using 4.0.10-gamma as mysql monitor indicates. Also:
$ mysqld --version mysqld Ver 4.0.10-gamma for mandrake-linux-gnu on i586
You should install MySQL-Max binary if you want to use InnoDB: http://www.mysql.com/doc/en/InnoDB_in_MySQL_3.23.html
This doesn't apply as MySQL version is 4.0.10.
Any other ideas? -- Hidden DOS secret: add BUGS=OFF to your CONFIG.SYS
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
