472,809 Members | 2,520 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,809 software developers and data experts.

foreign key contraints, on delete cascade not working?

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.

Jul 19 '05 #1
1 8763
Andrew,

you should send your MySQL questions to my***@lists.mysql.com. This
newsgroup is not as active as that list.

Please check with

SHOW VARIABLES LIKE '%innodb%';

that you really have InnoDB enabled.

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" <An****@DeFaria.com>
Newsgroups: mailing.database.mysql
Sent: Saturday, January 10, 2004 7:03 AM
Subject: foreign key contraints, on delete cascade not working?

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.

"Andrew DeFaria" <An****@DeFaria.com> kirjoitti viestissä
news:1d***************************@msgid.meganewss ervers.com... 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.

Jul 19 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Jeremiah Jacks | last post by:
I just upgraded to MySQL 4.0.14-standard for RedHat Linux and am using = the pre-compiled binaries. I have a database with INNODB tables. When I insert a row into one of the child tables, I get...
5
by: Andrew DeFaria | last post by:
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...
2
by: Gunnar Vøyenli | last post by:
Hi! For the sake of simplicity, I have three tables, Employee, Department and Work Employee >---- Department \ / \ / ^ ^ Work
0
by: Rob Panosh | last post by:
Hello, If I have table the following table structure: Table Customer ( CustomerId Numeric(10,0) Not Null, ... ) Table CustomerOrders ( CustomerOrderId As Numeric(10,0) Not Null,...
1
by: Jason Madison | last post by:
We sometimes get very large databases that we want to cut down to use for testing. The information is all related to a central accounts table. The way I thought of doing this is to grab all...
31
by: Robert Brown | last post by:
Let's say I have a type hierarchy: (just an example) the general entity customer: CREATE TABLE customer(customer_id int, customer_name varchar(250), customer_type int) three specific...
14
by: Karl O. Pinc | last post by:
Hi, Thought perhaps some other eyes than mine can tell if I'm doing something wrong here or if there's a bug somewhere. I've never passed a ROWTYPE varaible to a function but I don't see where...
0
by: mrhodes02 | last post by:
I have two simple tables Employees (primary key table) Empid, empname,title MgrXrf (foreign key table) MgrId,Empid MgrID & Empid in MgrXrf have a foreign contstrant to EmpID in Employees....
5
by: Bob Stearns | last post by:
For good and sufficient reasons I wish to insure that a primary key of table 1 is not a primary key of table 2. The following does not work: ALTER TABLE IS3.AUCTION_SUPER_CATEGORIES ADD...
1
by: poopsy | last post by:
hello all i am getting confused with foreign key constraints.. i have the following table: -- -- Table structure for table `reviewer` -- CREATE TABLE `reviewer` (
0
linyimin
by: linyimin | last post by:
Spring Startup Analyzer generates an interactive Spring application startup report that lets you understand what contributes to the application startup time and helps to optimize it. Support for...
0
by: erikbower65 | last post by:
Here's a concise step-by-step guide for manually installing IntelliJ IDEA: 1. Download: Visit the official JetBrains website and download the IntelliJ IDEA Community or Ultimate edition based on...
0
by: kcodez | last post by:
As a H5 game development enthusiast, I recently wrote a very interesting little game - Toy Claw ((http://claw.kjeek.com/))。Here I will summarize and share the development experience here, and hope it...
0
by: Taofi | last post by:
I try to insert a new record but the error message says the number of query names and destination fields are not the same This are my field names ID, Budgeted, Actual, Status and Differences ...
14
DJRhino1175
by: DJRhino1175 | last post by:
When I run this code I get an error, its Run-time error# 424 Object required...This is my first attempt at doing something like this. I test the entire code and it worked until I added this - If...
0
by: Rina0 | last post by:
I am looking for a Python code to find the longest common subsequence of two strings. I found this blog post that describes the length of longest common subsequence problem and provides a solution in...
0
by: lllomh | last post by:
How does React native implement an English player?
0
by: Mushico | last post by:
How to calculate date of retirement from date of birth
2
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.