473,883 Members | 2,177 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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 8915
Andrew,

you should send your MySQL questions to my***@lists.mys ql.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.databas e.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.mega newsservers.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
2644
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 the following = MySQL error: INSERT INTO product_access_level (product_id,access_level_id) VALUES
5
2845
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 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:
2
39438
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
18828
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, CustomerId Numeric(10,0) Not Null, CustomerRefId Numeric (10,0) Not Null, ... )
1
5283
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 the foreign constraints and turn them into cascade delete constraints, then delete as many accounts as I want. After this I will restore the constraints back to their original state.
31
3402
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 customer subtypes: 1 - business, 2 - home, 3 - university
14
5859
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 the problem is. I keep getting errors like (the first is my debug output): NOTICE: last cycle is: 11 WARNING: Error occurred while executing PL/pgSQL function
0
2702
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. That works fine. I don' want to add a record to MgrXRF unless both field values exist in Employees. Problem is I also want to set the delete rule to cascade on both fields, so that if I delete an employee their record is deleted from MgrXRF no...
5
2504
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 CONSTRAINT code_not_fk check(code not in (select code from IS3.AUCTION_CATEGORIES where auction_id=auction_id)) Is there any way other than a trigger (or program checks) to write this?
1
2143
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
11160
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10766
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
10422
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
7978
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7136
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5807
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
6007
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4622
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4230
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

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.