MySql查询错误150之谜

Question

我必须为类(class)项目创建和使用数据库。我使用 mysql workbench 在本地开发了数据库和应用程序。它需要托管在另一个名为 myphpmyadmin 的服务上。问题是当我使用该服务中的 mysql 执行行创建表时，出现以下错误。

 Error
SQL query:

CREATE TABLE IF NOT EXISTS  `artists_residence` (

 `artists_id` INT( 11 ) ,
 `city_id` INT( 11 ) ,
PRIMARY KEY (  `artists_id` ,  `city_id` ) ,
CONSTRAINT FOREIGN KEY (  `artists_id` ) REFERENCES  `artists` (  `id` ) ON DELETE CASCADE ON UPDATE CASCADE ,
CONSTRAINT FOREIGN KEY (  `city_id` ) REFERENCES  `city` (  `id` ) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = INNODB CHARSET = Latin1;

MySQL said: Documentation

#1005 - Can't create table 'fondellb-db.artists_residence' (errno: 150) 

这是我试图执行的 mySql 的整个文件。

CREATE TABLE IF NOT EXISTS `artists` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `fname` VARCHAR(255) NOT NULL,
  `lname` VARCHAR(255) NOT NULL,
  `year_of_birth` DATE NOT NULL,
  `year_of_death` DATE NULL DEFAULT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB CHARSET = Latin1;


CREATE TABLE IF NOT EXISTS `country` (
  `country_id` INT(11) NOT NULL,
  `name` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`country_id`))
ENGINE = InnoDB CHARSET = Latin1;


CREATE TABLE IF NOT EXISTS `city` (
  `id` INT(11) NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `fk_country_id` INT(11),
  PRIMARY KEY (`id`, `fk_country_id`),
  CONSTRAINT `country_id`
    FOREIGN KEY (`fk_country_id`)
    REFERENCES `country` (`country_id`)
    ON DELETE RESTRICT
    ON UPDATE RESTRICT)
ENGINE = InnoDB CHARSET = Latin1;

CREATE TABLE IF NOT EXISTS `Medium` (
  `id` INT(11) NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB CHARSET = Latin1;


CREATE TABLE IF NOT EXISTS `artists_medium` (
  `artists_id` INT(11),
  `medium_id` INT(11),
  PRIMARY KEY (`artists_id`, `medium_id`),
  CONSTRAINT 
    FOREIGN KEY (`artists_id`)
    REFERENCES `artists` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT 
    FOREIGN KEY (`medium_id`)
    REFERENCES `Medium` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB CHARSET = Latin1;

CREATE TABLE IF NOT EXISTS `artists_residence` (
  `artists_id` INT(11),
  `city_id` INT(11),
  PRIMARY KEY (`artists_id`, `city_id`),
  CONSTRAINT 
    FOREIGN KEY (`artists_id`)
    REFERENCES `artists` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT 
    FOREIGN KEY (`city_id`)
    REFERENCES `city` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB CHARSET = Latin1;

我无法弄清楚最终查询中失败的原因。所有其他查询均成功并且表已创建。我已经用各种变体摆弄了该查询，但似乎没有什么可以解决该错误。我删除了显式索引，更改了约束的顺序，检查了所有类型比较。这是 mysql 根据我设计的模型导出的文件，所以我不确定为什么现在收到此错误。请不要向我推荐对错误代码的引用或也收到此错误代码的另一篇文章，我知道什么会导致该错误，但我想知道是什么导致了这里的错误。

Answer 1

在本例中 - 您将创建以下外键

CONSTRAINT FOREIGN KEY (  `city_id` ) REFERENCES  `city` (  `id` ) ON DELETE CASCADE ON UPDATE CASCADE

而city表没有id主键，而是复合主键。

显然，2 列中唯一的一列不能保证值的唯一性，因此 mysql 限制您创建这样的 FK。

因此您必须更改city的PK或artists_residence的FK。