Table not cascading on update or delete
Posted by: Anders Ingemann
Date: March 09, 2006 09:12AM

Hey guys,

I am currently working on a fileserver project and designing the database with DBDesigner 4.
I decided to run the tables with InnoDB in order to maintain data integrity.

In this db model I have two tables. One called users and one called folders.
The idea is to not let anything delete the user as long as there are files (or folders in this case), which are associated with him.
Right now I am trying it with CASCADE, meaning that the entries in the folders table are in fact being deleted when the user gets deleted.

Now here's the problem:
If I manually create the tables, it all works just fine.
This is the script:

CREATE TABLE users (
id int(10) unsigned NOT NULL auto_increment,
name char(255) NOT NULL,
pwd char(255) NOT NULL,
PRIMARY KEY (id),
UNIQUE KEY name (name)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

CREATE TABLE folders (
id int(10) unsigned NOT NULL auto_increment,
users_id int(10) unsigned NOT NULL,
name char(255) default NULL,
parent int(10) unsigned default NULL,
PRIMARY KEY (id),
UNIQUE KEY folder (parent,name),
INDEX(users_id),
FOREIGN KEY (users_id)
REFERENCES users(id)
ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1

If I run "SHOW CREATE TABLE folders" and "SHOW CREATE TABLE users" on the tables DBDesigner created I get the following:
CREATE TABLE `users` (
`id` int(10) unsigned NOT NULL auto_increment,
`name` char(255) NOT NULL,
`pwd` char(255) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

CREATE TABLE `folders` (
`id` int(10) unsigned NOT NULL auto_increment,
`users_id` int(10) unsigned NOT NULL,
`name` char(255) default NULL,
`parent` int(10) unsigned default NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `folder` (`parent`,`name`),
KEY `folders_FKIndex1` (`users_id`),
CONSTRAINT `folders_ibfk_1` FOREIGN KEY (`users_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1

I don't get it. The only significant difference between those two scripts are me using INDEX instead of KEY (what's the difference btw?) and DBDesigner explicitly specifying the name of the constraint.

This thing is really giving me headaches, please help me to get rid of them ;-)



Edited 1 time(s). Last edit at 03/09/2006 09:13AM by Anders Ingemann.

Options: ReplyQuote


Subject
Written By
Posted
Table not cascading on update or delete
March 09, 2006 09:12AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.