CODEWITHSUNDEEP
mysql
IRHM
IRHM

Reputation: 1326

MySQL Delete Cascade

I wonder whether someone may be able to help me please.

I'm wanting to use the 'Delete Cascade' mySQL functionality so that when a user is deleted from the 'user details' table, it deletes their records from the other tables in my database.

After reading through some posts on Stackoverflow and from research I've carried out on the Internet, I've changed my tables to InnoDB and started to change my existing tables.

I've been able to add the 'Delete Cascade With Foreign Key' to my first table, but when I try to do the same to any other table, I receive the following error:

#1005 - Can't create table './db369054642/#sql-30d_bd1a57.frm' (errno: 121)

But I'm not sure why I'm receiving this error because the first table that I changed worked without a hitch.

Could someone tell me please, are you only able to link the parent table to one child table?

Parent Table (User Details)

DROP TABLE IF EXISTS `userdetails`;
CREATE TABLE IF NOT EXISTS `userdetails` (
  `userid` int(6) NOT NULL auto_increment,
  `forename` varchar(20) NOT NULL,
  `surname` varchar(30) NOT NULL,
  `emailaddress` varchar(150) NOT NULL,
  `password` varchar(200) NOT NULL,
  `passwordhint` varchar(20) NOT NULL,
  `subscriptionexpiration` date NOT NULL,
  `salt` varchar(200) NOT NULL,
  PRIMARY KEY  (`userid`),
  UNIQUE KEY `emailaddress` (`emailaddress`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

Child Table (detectors)

DROP TABLE IF EXISTS `detectors`;
CREATE TABLE IF NOT EXISTS `detectors` (
  `userid` int(6) NOT NULL,
  `detectorid` int(6) NOT NULL auto_increment,
  `detectorname` varchar(30) NOT NULL,
  PRIMARY KEY  (`detectorid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 PACK_KEYS=0 AUTO_INCREMENT=1 ;

Delete Cascade SQL Statement

ALTER TABLE detectors
   add CONSTRAINT fk_userdetails
     FOREIGN KEY (userid)
     REFERENCES userdetails(userid)
     ON DELETE CASCADE

Upvotes: 1

Views: 7800

Answers (2)

Emmet O'Grady
Emmet O'Grady

Reputation: 269

Have a look at the perror utility (it's included with mysql distibutions). It gives a more detailed explanation for error codes and is run from your command line. The error number in your case is 121, so you'd run this on the command line:

perror 121

http://dev.mysql.com/doc/refman/5.0/en/perror.html

Upvotes: 2

Konerak
Konerak

Reputation: 39763

Do both your table and the referenced table have indexes on the involved columns?

From http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html:

InnoDB requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan. In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order. Such an index is created on the referencing table automatically if it does not exist. (This is in contrast to some older versions, in which indexes had to be created explicitly or the creation of foreign key constraints would fail.) index_name, if given, is used as described previously.

Try to create the necessary indexes first, or show us the output from the SHOW CREATE TABLE yourTableName so we can check if the indexes exist and have no name conflicts.

Upvotes: 2

Related Questions