CODEWITHSUNDEEP
mysqlsqlforeign-keysforeign-key-relationship
TiMESPLiNTER
TiMESPLiNTER

Reputation: 5889

Error 1005 Can't create table for adding a foreign key

I've two InnoDB tables in a MySQL database. Now I like to link one table to the other with a FOREIGN KEY. But it aborts with the error code 1005 - Can't create table.

I've found plenty of information about this error. For example this post and answer here on SO. Although even this answer did not help in my case. I think every point which is mentioned to be checked is not true in my case.

I've created a MySQLFiddle you can checkout with the schema which fails: http://sqlfiddle.com/#!2/b9dc43

(Please note that the failing ALTER TABLE for adding the FOREIGN KEY is not included. See the schema below for the FOREIGN KEY query.)

There's also a FK for cms_element_instance.page_IDFK which references column page.ID but I removed this FK for posting my problem here on SO.

What can be the problem here?

Here the schema in plain text:

CREATE TABLE `cms_element_instance` (
 `ID` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
 `page_IDFK` mediumint(8) unsigned DEFAULT NULL,
 `revision` varchar(16) DEFAULT NULL,
 PRIMARY KEY (`ID`),
 KEY `page_IDFK` (`page_IDFK`)
) ENGINE=InnoDB AUTO_INCREMENT=1692 DEFAULT CHARSET=utf8;

CREATE TABLE `element_faq_list` (
 `element_instance_IDFK` mediumint(8) unsigned NOT NULL,
 `page_IDFK` mediumint(8) unsigned NOT NULL,
 `mod_faq_collection_IDFK` mediumint(8) unsigned NOT NULL,
 PRIMARY KEY (`element_instance_IDFK`,`page_IDFK`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


-- Create FK: This throws error 1005
ALTER TABLE element_faq_list
ADD CONSTRAINT element_instance_fk
FOREIGN KEY (element_instance_IDFK, page_IDFK)
REFERENCES cms_element_instance( ID , page_IDFK);

Upvotes: 1

Views: 988

Answers (1)

M Khalid Junaid
M Khalid Junaid

Reputation: 64476

You are adding (element_instance_IDFK, page_IDFK) as a foreign key constraints which refers to ID , page_IDFK columns from cms_element_instance so for this case you need a primary key based on ID , page_IDFK columns 

ALTER TABLE `cms_element_instance` CHANGE `page_IDFK` `page_IDFK` 
MEDIUMINT(8) UNSIGNED NOT NULL, DROP PRIMARY KEY, 
ADD PRIMARY KEY (`ID`, `page_IDFK`);

Demo

Upvotes: 3

Related Questions