Table not cascading on update or delete
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.