MYSQL 外键

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    |                |
+-------+------------------+------+-----+---------+----------------+