0

I want to use cascading in my project to simplify certain processes. I created these two queries as well as a couple more but all child tables throw the same error on execution. They worked when I used MyISAM as the engine, but on further testing and research I figured that it does not support cascading, so I switched the engine to InnoDB which triggered these errors. I've looked at a couple of forums and threads with the same problem but I just can't seem to figure out where the actual problem lies. Can someone help?

ParentTable:

CREATE TABLE IF NOT EXISTS `branches` ( `branch_id` int(11) NOT NULL AUTO_INCREMENT, `key` VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, `name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, `short_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, `city` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, `timezone` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, UNIQUE(`key`), PRIMARY KEY (`branch_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8

Child Table:

CREATE TABLE IF NOT EXISTS `files` ( `file_id` int(11) NOT NULL AUTO_INCREMENT, `branch_id` int(11) NOT NULL, `path` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, `use_google_analytics` BOOLEAN NOT NULL, FOREIGN KEY(`branch_id`) REFERENCES `branches`(`branch_id`) ON DELETE CASCADE ON UPDATE CASCADE, PRIMARY KEY (`file_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8

Error:

1005: Can't create table 'files' (errno: 150)
Ke Vin
  • 2,004
  • 1
  • 18
  • 28
  • It's not a collation issue, it's a foreign key definition issue. Have a look to this question: http://stackoverflow.com/questions/4061293/mysql-cant-create-table-errno-150 – Ander2 Jun 20 '13 at 13:00
  • Alright thanks, I fixed the title. – Ke Vin Jun 20 '13 at 13:12

1 Answers1

0

You defined your foreign key

ON DELETE CASCADE

But your branch_id is defined as NOT NULL. That won't work.

You want your branch_id never be null but then the foreign key related data gets removed you want it set to NULL with the CASCADE option.

So either change removd the ON DELETE CASCADE or allow NULL in the foreign key. This works

SQLFiddle demo

Community
  • 1
  • 1
juergen d
  • 201,996
  • 37
  • 293
  • 362
  • Can you show me what I have to alter in my case? I cannot remove the ON DELETE CASCADE since that is the exact behavior I need. Simply changing NOT NULL to NULL or remove it completely did not work. – Ke Vin Jun 20 '13 at 13:35
  • Thank you, I just tested everything again and this time it seems to execute correctly even with both keys set to NOT NULL – Ke Vin Jun 20 '13 at 14:45