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

Question

这是我的 table

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 ;

Answer 1

您的数据类型不匹配，因为您的主表使用 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;