mysql外键为主键报错

Question

我的 SQL 中遇到外键错误。我的 mySQL 表是

create table if not exists table1(
cust_name varchar(30) not null,
cust_phone char(16) not null,
cust_mail char(30) not null,
cust_address varchar(100),
bills int(10),
primary key (cust_name)
)ENGINE=INNODB;

create table if not exists table2 (
camp_id varchar(30) not null,
advr_id varchar(30) not null,
foreign key (advr_id) references table1 (cust_name),
primary key (camp_id)
)ENGINE=INNODB;

create table if not exists table3(
ad_id varchar(30) not null,
camp_id varchar(30) not null,
foreign key (camp_id) references table2(camp_id),
primary key (ad_id)
)ENGINE=INNODB;

create table if not exists counter(
ad_id varchar(30) not null,
foreign key (ad_id) references table3(ad_id),
PRIMARY KEY(ad_id)
)ENGINE=INNODB;

当我运行上述命令时，出现以下错误。

ERROR 1215 (HY000) at line 96: Cannot add foreign key constraint

问题是什么？

Answer 1

没有任何错误。运行没有错误。

mysql> create table if not exists table1(
    -> cust_name varchar(30) not null,
    -> cust_phone char(16) not null,
    -> cust_mail char(30) not null,
    -> cust_address varchar(100),
    -> bills int(10),
    -> primary key (cust_name)
    -> )ENGINE=INNODB;
Query OK, 0 rows affected, 1 warning (0.08 sec)


mysql> create table if not exists table2 (
    -> camp_id varchar(30) not null,
    -> advr_id varchar(30) not null,
    -> foreign key (advr_id) references table1 (cust_name),
    -> primary key (camp_id)
    -> )ENGINE=INNODB;
Query OK, 0 rows affected, 1 warning (0.10 sec)


mysql> create table if not exists table3(
    -> ad_id varchar(30) not null,
    -> camp_id varchar(30) not null,
    -> foreign key (camp_id) references table2(camp_id),
    -> primary key (ad_id)
    -> )ENGINE=INNODB;
Query OK, 0 rows affected (0.37 sec)


mysql> create table if not exists counter(
    -> ad_id varchar(30) not null,
    -> foreign key (ad_id) references table3(ad_id),
    -> PRIMARY KEY(ad_id)
    -> )ENGINE=INNODB;
Query OK, 0 rows affected (0.42 sec)

我在我的数据库中执行但没有任何错误或警告