Foreign constraint On Delete Cascade not working

Question

I have a custom table created which has a foreign constraint on the core_website table. However, the on delete cascade isn't working.

I did a search and found this relevant thread, which notes that the data types between the two columns have to be the same. Both data types are smallint(5).

I did notice one minor discrepancy in the column definition, which is that in core_website, Allow Null is not set, and Default is not set to zero, whereas in the account table, Allow Null is set and Default is zero. I didn't think changing these would have any effect, but I went ahead and changed them on the account table to match, but that didn't help.

CREATE TABLE `account` (
  `account_id` smallint(11) unsigned NOT NULL AUTO_INCREMENT,
  `website_id` smallint(5) unsigned DEFAULT '0',
  `code` varchar(64) NOT NULL DEFAULT '',
  PRIMARY KEY (`account_id`),
  UNIQUE KEY `code` (`code`),
  KEY `FK_WEBSITE_ID` (`website_id`),
  CONSTRAINT `FK_WEBSITE_ID` FOREIGN KEY (`website_id`) REFERENCES `core_website` (`website_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=44 DEFAULT CHARSET=utf8

So, if you delete row from `core_website` table related rows in `account` table aren't deleted, right? — Dmytro Zavalkin, Mar 20 '12 at 07:23
Actually other way around, I'm deleting from the account table and wanting the core_website record deleted. — kalenjordan, Mar 20 '12 at 16:13
In this case you need add foreign key to `core_website` table and point it to `website_id` field in `account` table. In other words, foreign key should be added to dependent table and pointed to main table, in this case when you delete row from main table - row from dependent table will be deleted because of FK. In your case you did it "upside down". — Dmytro Zavalkin, Mar 20 '12 at 20:43
Thanks, that's it. You should post your comment as an answer so I can mark it correct. — kalenjordan, Mar 21 '12 at 00:13

score 1 · Answer 1 · answered Mar 20 '12 at 11:54

1

try this and let us the result pls.

CREATE TABLE `account` (
  `account_id` smallint(11) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `website_id` smallint(5) unsigned DEFAULT '0',
  `code` varchar(64) NOT NULL DEFAULT '',
  UNIQUE KEY `code` (`code`),
  KEY `FK_WEBSITE_ID` (`website_id`),
  CONSTRAINT `FK_WEBSITE_ID` FOREIGN KEY (`website_id`) REFERENCES `core_website` (`website_id`) ON DELETE CASCADE ON UPDATE CASCADE  
) ENGINE=InnoDB AUTO_INCREMENT=44 DEFAULT CHARSET=utf8

answered Mar 20 '12 at 11:54

Oğuz Çelikdemir

4,990
4
30
56

Hi Oguz, so the only difference being that "PRIMARY KEY" is included in-line with the account_id field definition, as opposed to on a separate line? – kalenjordan Mar 20 '12 at 16:14
yes, mysql ignores inline foreign key constraints, so you should explicitly define. – Oğuz Çelikdemir Mar 20 '12 at 20:38
Thanks Oguz, I think I remember reading a thread where an inline foreign key constraint was an issue. But actually I don't think my foreign key constraint is inline, right? You were suggesting that I make my PRIMARY KEY definition inline, but my primary key is also working just fine. Looks like @zyava had the answer, I was a little confused about which direction foreign constraints work. Thanks for your help though! – kalenjordan Mar 21 '12 at 00:14

score 0 · Accepted Answer · answered Mar 21 '12 at 09:15

You need add foreign key to core_website table and point it to website_id field in account table. In other words, foreign key should be added to the dependent table and pointed to main table, in this case when you delete row from main table - row from dependent table will be deleted because of FK. In your case you did it "upside down".

Foreign constraint On Delete Cascade not working

2 Answers2