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

I have the following SQL script. After it runs the foreign key relationship is never enforced.

我有以下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);

Is this because of the composite primary key?

这是因为复合主键吗?

4 个解决方案

#1

According to the MySQL docs on foreign keys, The MyISAM table engine does not support foreign keys. Instead it silently ignores them. Use InnoDB instead. Try this:

根据外键上的MySQL文档,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);

#2

Only the InnoDB engine supports foreign keys:

只有InnoDB引擎支持外键:

InnoDB supports foreign key constraints.

InnoDB支持外键约束。

#3

It had nothing to do with that, as per this article

根据这篇文章,它与此无关

http://dev.mysql.com/doc/refman/5.0/en/example-foreign-keys.html

A foreign key constraint is not required merely to join two tables. For storage engines other than InnoDB, it is possible when defining a column to use a REFERENCES tbl_name(col_name) clause, which has no actual effect, and serves only as a memo or comment to you that the column which you are currently defining is intended to refer to a column in another table. It is extremely important to realize when using this syntax that:

仅仅为了连接两个表,不需要外键约束。对于除InnoDB之外的存储引擎,可以在定义列时使用REFERENCES tbl_name(col_name)子句,该子句没有实际效果,并且仅作为备忘录或注释,表示您当前定义的列是预期的引用另一个表中的列。在使用以下语法时要意识到:

#4

@Benju.

You have to replace MyISAM with InnoDB and then you can use Primary key as a composite or single. It works and i have tested.

您必须使用InnoDB替换MyISAM,然后您可以将主键用作复合或单个。它工作,我已经测试过。

#1