mysql - 我们如何从两个表中进行引用？ MySQL的

这是我的桌子

CREATE TABLE IF NOT EXISTS `carslibrary` (
  `CarID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `CarName` varchar(255) NOT NULL,
  PRIMARY KEY (`CarID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=12 ;

CREATE TABLE IF NOT EXISTS `colorslibrary` (
  `ColorID` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `ColorName` varchar(255) NOT NULL,
  PRIMARY KEY (`ColorID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;

我想参考CarID和ColorID创建另一个表

我做了一张叫facerecord的桌子

CREATE TABLE IF NOT EXISTS `facerecord` (
  `carslibrary_ID` int(10) NOT NULL,
  `colorslibrary_ID` int(11) NOT NULL,
  KEY `carslibrary_ID` (`carslibrary_ID`),
  KEY `colorslibrary_ID` (`colorslibrary_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

我收到此错误

Error creating foreign key on carslibrary_ID (check data types)

Error creating foreign key on colorslibrary_ID (check data types)

Error
ALTER TABLE  `facerecord` ADD FOREIGN KEY (  `carslibrary_ID` ) REFERENCES  `cars2`.`carslibrary` (
`CarID`
) ON DELETE SET NULL ON UPDATE SET NULL ;

ALTER TABLE  `facerecord` ADD FOREIGN KEY (  `colorslibrary_ID` ) REFERENCES  `cars2`.`colorslibrary` (
`ColorID`
) ON DELETE SET NULL ON UPDATE SET NULL ;

最佳答案

数据类型不匹配，因为主表使用unsigned整数类型，但是facerecord表使用纯符号INT(10), INT(11)作为数据类型。

将facerecord更改为：

CREATE TABLE IF NOT EXISTS `facerecord` (
  `carslibrary_ID` int(10) unsigned NOT NULL,
  `colorslibrary_ID` int(11) unsigned NOT NULL,
  KEY `carslibrary_ID` (`carslibrary_ID`),
  KEY `colorslibrary_ID` (`colorslibrary_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

关于mysql - 我们如何从两个表中进行引用？ MySQL的，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/7669489/