Foreign key with composite primary key not working in MySql

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

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
开发者_JS百科foreign key (countryName)
references Country (name);

Is this because of the composite primary key?

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:

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);

Only the InnoDB engine supports foreign keys:

InnoDB supports foreign key constraints.

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:

@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.