Ronny Linsener February 2016

Can not set FOREIGN KEY with DELETE ON CASCADE

I would like delete referencing data with a FOREIGN KEY.

Here are two of my datatables:

CREATE TABLE `specification_variant_parent` (
  `specification_variant_parent_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `sort_order` tinyint(3) unsigned NOT NULL,
  PRIMARY KEY (`specification_variant_parent_id`)
) ENGINE=InnoDB;


CREATE TABLE `specification_variant_parent_description` (
  `specification_variant_parent_id` smallint(5) unsigned NOT NULL,
  `language_id` tinyint(3) unsigned NOT NULL,
  `name` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`specification_variant_parent_id`,`language_id`),
  KEY (`specification_variant_parent_id`)
  REFERENCES specification_variant_parent (specification_variant_parent_id) ON DELETE CASCADE
) ENGINE=InnoDB;

When I fire it in phpMyAdmin it says:

Error in foreign key constraint of table database/specification_variant_parent_description: FOREIGN KEY (specification_variant_parent_id) REFERENCES specification_variant_parent (specification_variant_parent_id) ON DELETE CASCADE ) ENGINE=InnoDB: Cannot find an index in the referenced table where the referenced columns appear as the first columns, or column types in the table and the referenced table do not match for constraint.

What is wrong with my database scheme?

Answers


ArturoAP February 2016

Your datatypes do not match.

In the first table you have

`specification_variant_parent_id` int(11) unsigned NOT NULL AUTO_INCREMEN

The datatype you declare is an unsigned int

In the second table the foreign key is

`specification_variant_parent_id` smallint(5) unsigned NOT NULL

and the datatype is an unsigned smallint

Make both of them the same datatype.

Post Status

Asked in February 2016
Viewed 3,054 times
Voted 4
Answered 1 times

Search




Leave an answer