mysql添加外键保存失败,在MySQL5中添加复合外键失败

我试图强制用户的地址信息中的州/省和国家名称来自我列出国家和州/省的一组表格.为了做到这一点,我尝试运行这样的alter table命令……

ALTER TABLE User

ADD FOREIGN KEY (stateProvince,country)

REFERENCES `StateProvince`(`name`,`countryName`);

然后我收到这条消息……

Create table ‘realtorprint_dev/#sql-d5c_3d’ with foreign key

constraint failed. There is no index in the referenced table where the

referenced columns appear as the first columns.

有没有人知道如何处理此错误消息？

这是州和国家表的创建……

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 constraint FK_StateProvince_Country

foreign key (countryName)

references Country (name);

现在对于用户表…

create table realtorprint_dev.user (

id bigint not null,

created datetime,

email varchar(255) not null,

fax varchar(255),

mobile varchar(255),

name varchar(255),

password varchar(255),

phone varchar(255),

title varchar(255),

tollFree varchar(255),

updated datetime,

web varchar(255),

brokerage_id bigint,

address varchar(255),

city varchar(255),

country varchar(255),

stateProvince varchar(255),

type varchar(255),

zipPostal varchar(255),

activated bit not null,

locked bit not null,

primary key (id),

foreign key FK285FEB6722226 (brokerage_id) references brokerage(id)

);