CODEWITHSUNDEEP
mysqlsqlinnodb
ndg
ndg

Reputation: 2645

Adding table with FOREIGN KEY to a MySQL database causes errno 150

I'm attemping to execute the following SQL and am receiving errno: 150 'cannot create table path_relations' in response. According to the MySQL documentation this is caused by my FOREIGN KEY restraints having issues. What am I doing wrong?

DROP TABLE IF EXISTS `paths`;
DROP TABLE IF EXISTS `path_relations`;

CREATE TABLE `paths` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(256) CHARACTER SET utf8 DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `path_relations` (
    `ancestor` int(11) NOT NULL DEFAULT '0',
    `descendant` int(11) NOT NULL DEFAULT '0',
    PRIMARY KEY(`ancestor`, `descendant`),
    FOREIGN KEY(`ancestor`) REFERENCES paths(`id`),
    FOREIGN KEY(`descendant`) REFERENCES paths(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Upvotes: 1

Views: 98

Answers (3)

Timmetje
Timmetje

Reputation: 7694

Here is a checklist for you, good luck ;)

1) type of foreign key source and reference fields must be identical

2) both source and reference fields must be unsigned

3) source field must be indexed

4) both tables must be InnoDB

Upvotes: 4

Jason Swett
Jason Swett

Reputation: 45094

Does it work if you make paths.id not unsigned?

Upvotes: 3

idodev
idodev

Reputation: 334

UPDATED: In the first table you define your integer value as unsigned whilst in the second you haven't. The fields must be identical in structure to satisfy a foreign key.

Do you have any data in the table already? if so make sure that all records would satisfy the constraint. NULL values in the foreign keyed column will prevent this from working.

Upvotes: -1

Related Questions