CODEWITHSUNDEEP
mysqlforeign-keysforeign-key-relationship
manou
manou

Reputation: 133

Figure out a FK creation error on Mysql

I would need double eyes to see why I can't create the following Foreign Key.

ALTER TABLE `DEFAULTS_OPTIONS`
ADD CONSTRAINT `DEFAULTS_OPTIONS_ibfk_1` FOREIGN KEY (`univers_code`) REFERENCES `UNIVERSES` (`code`) ON DELETE CASCADE ON UPDATE CASCADE;

Here is the description of the tables involucrated:

CREATE TABLE `DEFAULTS_OPTIONS` (
`country_code` varchar(2) COLLATE utf8_unicode_ci NOT NULL,
`univers_code` varchar(2) COLLATE utf8_unicode_ci NOT NULL,
`element_code` varchar(3) COLLATE utf8_unicode_ci NOT NULL,
`option_code` varchar(8) COLLATE utf8_unicode_ci NOT NULL,
`element_category` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`country_code`,`univers_code`,`element_code`,`option_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `UNIVERSES` (
`country_code` varchar(2) COLLATE utf8_unicode_ci NOT NULL,
`code` varchar(2) COLLATE utf8_unicode_ci NOT NULL,
`description` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`image` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`order` tinyint(4) NOT NULL,
PRIMARY KEY (`country_code`,`code`),
CONSTRAINT `UNIVERSES_ibfk_1` FOREIGN KEY (`country_code`) REFERENCES `COUNTRY` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

And this is the error I get: 

Error Code: 1005. Can't create table 'DEV_DEV.#sql-aa2_2e1' (errno: 150)

Does anybody see my error?

Thanks in advance for taking a look.

EDIT:

Naktibalda adviced me to examine the result of 

SHOW ENGINE INNODB STATUS

to help to figure out the reason.

Upvotes: 0

Views: 28

Answers (2)

Ravinder Reddy
Ravinder Reddy

Reputation: 24002

To define simple, uncombined foreign keys, parent column must also be indexed.

It seems UNIVERSES.code column is not indexed.

Change it as follows: 

ALTER TABLE UNIVERSES ADD KEY ( code );

Now you can add foreign key to DEFAULTS_OPTIONS table on UNIVERSES.code column.

Upvotes: 1

Naktibalda
Naktibalda

Reputation: 14110

It fails because you don't have an index for UNIVERSES.code field, your PRIMARY KEY is not good for that because code isn't the first field in PK.

If you want to refer to a combination of 2 fields, use

ALTER TABLE `DEFAULTS_OPTIONS`
ADD CONSTRAINT `DEFAULTS_OPTIONS_ibfk_1` FOREIGN KEY (`country_code`, `univers_code`) REFERENCES `UNIVERSES` (`country_code`, `code`) ON DELETE CASCADE ON UPDATE CASCADE;

Upvotes: 1

Related Questions