mysql - 具有复合主键的外键在 MySql 中不起作用

Question

我有以下 SQL 脚本。运行后，永远不会强制执行外键关系。

CREATE TABLE Country (
  name varchar(40) NOT NULL,
  abbreviation varchar(4) NOT NULL,
  PRIMARY KEY  (name)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

CREATE TABLE StateProvince (
  countryName varchar(40) NOT NULL,
  name varchar(100) NOT NULL,
  abbreviation varchar(3) NOT NULL,
  PRIMARY KEY  (countryName,name)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

alter table StateProvince
add index FK_StateProvince_Country (countryName),
add constraint FK_StateProvince_Country
foreign key (countryName)
references Country (name);

这是因为复合主键吗？

Answer 1

根据MySQL docs on foreign keys , MyISAM 表引擎不支持外键。相反，它会默默地忽略它们。请改用 InnoDB。试试这个:

CREATE TABLE Country (
  name varchar(40) NOT NULL,
  abbreviation varchar(4) NOT NULL,
  PRIMARY KEY  (name)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

CREATE TABLE StateProvince (
  countryName varchar(40) NOT NULL,
  name varchar(100) NOT NULL,
  abbreviation varchar(3) NOT NULL,
  PRIMARY KEY  (countryName,name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

alter table StateProvince
add index FK_StateProvince_Country (countryName),
add constraint FK_StateProvince_Country
foreign key (countryName)
references Country (name);