SET FOREIGN_KEY_CHECKS=0;
来禁用外键约束.
SET FOREIGN_KEY_CHECKS=1;
来启动外键约束.
在表tt(id)上建立外键，id不是主键 ，bb(bid)也不是主键 ，出错
mysql> alter table tt add constraint fk_tb foreign key(id) references bb(bid) on delete cascade;
ERROR 1005 (HY000): Can't create table 'info.#sql-fa8_1d5' (errno: 150)
创建表bb时建立外键 ，bb(bid)不是主键 tt(id)也不是主键，出错
mysql> create table bb(bid int,age int, foreign key(bid) references tt(id)on delete cascade);
ERROR 1005 (HY000): Can't create table 'info.bb' (errno: 150)
创建表bb时建立外键，bb(bid)是主键 tt(id)不是主键，出错
mysql> create table bb(bid int primary key,age int, foreign key(bid) references tt(id) on delete cascade);
ERROR 1005 (HY000): Can't create table 'info.bb' (errno: 150)
创建表bb时建立外键，bb(bid)是主键 tt(id)是主键，成功
mysql> create table bb(bid int primary key,age int, foreign key(bid) references tt(id)on delete cascade);
Query OK, 0 rows affected (0.05 sec)
查看表结构
mysql> desc tt;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name | varchar(16) | YES | MUL | NULL | |
| info | text | YES | | NULL | |
| id | int(11) | NO | PRI | NULL | |
+-------+-------------+------+-----+---------+-------+
mysql> desc bb;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| bid | int(11) | NO | PRI | NULL | |
| age | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
结论：互为外键的两个字段必须都是主键
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
建立表product表时cid 不是主键，但是company表的cid是主键，此时外键成功了，cid 的类型是MUL;
mysql> create table product(pid int unsigned auto_increment, cid int unsigned, name varchar(16),primary key(pid),foreign key(cid) references company(cid));
Query OK, 0 rows affected (0.05 sec)
mysql> desc product;
+-------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+
| pid | int(10) unsigned | NO | PRI | NULL | auto_increment |
| cid | int(10) unsigned | YES | MUL | NULL | |
| name | varchar(16) | YES | | NULL | |
+-------+------------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)
mysql> desc company;
+-------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+
| cid | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(16) | YES | | NULL | |
+-------+------------------+------+-----+---------+----------------+
如果建立product表是cid是主键，则外键也会成功，但cid的类型是PRI:
mysql> alter table product add constraint fk_pro foreign key(cid) references com
pany(cid) on delete cascade;
Query OK, 0 rows affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc product;
+-------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+
| pid | int(10) unsigned | NO | PRI | NULL | auto_increment |
| cid | int(10) unsigned | NO | PRI | 0 | |
| name | varchar(16) | YES | | NULL | |
+-------+------------------+------+-----+---------+----------------+