Heikki Tuuri wrote:
[color=blue]
> Andrew,
>
> you should send your MySQL questions to
mysql@lists.mysql.com. This
> newsgroup is not as active as that list.[/color]
Maybe but I don't do mail lists.
[color=blue]
> Please check with
>
> SHOW VARIABLES LIKE '%innodb%';
>
> that you really have InnoDB enabled.[/color]
Hmmm... Says have_innodb=no. 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'
Guess I have some more reading to to.
[color=blue]
> 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/
>
>
> heikki@hundin:~/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" <Andrew@DeFaria.com>
> Newsgroups: mailing.database.mysql
> Sent: Saturday, January 10, 2004 7:03 AM
> Subject: foreign key contraints, on delete cascade not working?
>
>[color=green]
>> 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.
>>[/color]
>
> "Andrew DeFaria" <Andrew@DeFaria.com> kirjoitti viestissä
> news:1d3f4$3fff87bf$44a7886c$12806@msgid.meganewss ervers.com...
>[color=green]
>> 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.
>>[/color]
>
>[/color]
--
If you mated a bulldog and a shitsu, would it be called a bullshit?
