ERROR 1215 (HY000): Cannot add foreign key constraint

Question

I have two tables and I want to create a foreign key but I've got ERROR 1215. Here are the tables.

 CREATE TABLE `entities_def` (
  `entityid` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Entity ID',
  `parentid` int(10) unsigned DEFAULT NULL COMMENT 'Parent Entity ID',
  `nick` varchar(255) NOT NULL COMMENT 'Entity Nickname',
  `esid` int(10) unsigned NOT NULL DEFAULT '1' COMMENT 'Entity State ID',
  `rdn` varchar(255) NOT NULL COMMENT 'Entity LDAP Relative Distinguished Name',
  `etype` enum('physical','legal','structural','external','access') CHARACTER SET ascii COLLATE ascii_bin NOT NULL DEFAULT 'physical' COMMENT 'Entity Type',
  `ctime` timestamp NULL DEFAULT NULL COMMENT 'Time of Creation',
  `mtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Time of Last Modification',
  `cby` int(10) unsigned DEFAULT NULL COMMENT 'Created By',
  `mby` int(10) unsigned DEFAULT NULL COMMENT 'Last Modified By',
  `descr` text COMMENT 'Entity Description',
  PRIMARY KEY (`entityid`),
  KEY `i_parentid` (`parentid`),
  KEY `i_nick` (`nick`),
  KEY `i_esid` (`esid`),
  KEY `i_cby` (`cby`),
  KEY `i_mby` (`mby`),
  CONSTRAINT `entities_def_fk_cby` FOREIGN KEY (`cby`) REFERENCES `users` (`uid`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `entities_def_fk_esid` FOREIGN KEY (`esid`) REFERENCES `entities_states` (`esid`) ON UPDATE CASCADE,
  CONSTRAINT `entities_def_fk_mby` FOREIGN KEY (`mby`) REFERENCES `users` (`uid`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `entities_def_fk_parentid` FOREIGN KEY (`parentid`) REFERENCES `entities_def` (`entityid`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8 COMMENT='Entities'

And the second one:

CREATE TABLE `userdomains` (
  `id` int(32) NOT NULL AUTO_INCREMENT,
  `accessuserid` int(10) NOT NULL,
  `domainname` char(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Domain Names'

I'm trying to create a foreign key constraint as follows:

alter table userdomains ADD CONSTRAINT userdomains_fk_accessuserid FOREIGN KEY (accessuserid) REFERENCES entities_def (entityid) ;

but an error does not dissapears.

After running SHOW ENGINE INNODB STATUS\G I got

    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.

but it looks like everything is fine, i'm referencing to parent's primary key, both columns are the same type and length... Can't get what is wrong.

Run SHOW ENGINE INNODB STATUS\G and look in the LATEST FOREIGN KEY ERROR section. — philipxy

e.thompsy e.thompsy · Accepted Answer · 2014-12-10T19:50:32

I had this problem and used desc TABLE; to examine my tables. I found that I was trying to create an FK relationship between a signed and an unsigned int of the same length (bigint(20) in my case). Remember, ints are signed by default and unsigned explicitly.

It looks like you are having the same problem:

userdomains.accessuserid is defined as int(10) NOT NULL,

while

entities_def.entityid is defined as int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Entity ID',

The only trouble with this relationship is that the two int(10) values are defined with different ranges and therefore are not compatible.

If I were you I would define them both as unsigned. Standard industry best practices dictate that AUTO_INCREMENTing Primary Keys should always start at zero.

ERROR 1215 (HY000): Cannot add foreign key constraint

2 Answers