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?
You should install MySQL-Max binary if you want to use InnoDB:
http://www.mysql.com/doc/en/InnoDB_in_MySQL_3.23.html
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Victoria Reznichenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net
<___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
