mysql怎么设主键和外键_MySQL基础之 主键外键设置

外键：

定义：如果表A的主关键字是表B中的字段，则该字段称为表B的外键，表A称为主表，表B称为从表。

作用：外键是用来实现参照完整性的，不同的外键约束方式将可以是两张表紧密的结合起来。比如修改或者删除的级联操作等，外键主要用来保证数据的完整性和一致性。

条件：

1、创建外键的两个表，父表与子表必须是InnoDB存储引擎。

2、创建外键的时候，父表要创建索引，一般为主键索引。子表在创建外键的时候也必须要创建索引。

3、子表的外键必须和父表的主键数据类型相对应(字段的类型和值必须一样或者相似，比如int也可以对应tinyint)。

语法：

下面这个是在子表中操作的：

[CONSTRAINT Symbol] FOREIGN KEY [id](index_col_name,.....)REFERENCEStbl_name (index_col_name,...)[ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]

[ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]

其中Symbol仅仅是给约束自定义一个名称，方便以后我们删除约束。

RESTRICT和NO  ACTION相同，是指限制在子表有关联记录的情况下父表不能进行操作。比如：DELETE  RESTRICT表示主表在删除记录的时候，如果子表有对应的记录，则不允许删除。比如UPDATE  CASCADE表示主表在更新记录的时候，如果子表有对应的记录，则子表也更新。(其实更新的就是主键和外键的那一列的值)。

CASCADE：表示父表在更新或者删除时，也更新或者删除子表对应的记录。

SET  NULL：表示父表的更新或者删除的时候，那么子表对应的字段被会SET  NULL。

注意：当某个表被其他表创建了外键参照，那么该表的对应索引或者主键禁止被删除。

例子：

首先是我们创建两个表，一个是父表，一个是字表。字表设置有参照约束。

mysql> create tablecountry(-> country_id smallint unsigned not nullauto_increment,-> country varchar(50) not null,-> last_update timestamp not null default current_timestamp on update current_timestamp,-> primary key(country_id)-> )engine=InnoDB default charset=utf8;

Query OK,0 rows affected (0.10sec)

mysql> desccountry;+-------------+----------------------+------+-----+-------------------+-----------------------------+

| Field | Type | Null | Key | Default | Extra |

+-------------+----------------------+------+-----+-------------------+-----------------------------+

| country_id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |

| country | varchar(50) | NO | | NULL | |

| last_update | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |

+-------------+----------------------+------+-----+-------------------+-----------------------------+

3 rows in set (0.00sec)mysql> create tablecity(-> city_id smallint unsigned not nullauto_increment,-> city varchar(50) not null,-> country_id smallint unsigned not null,-> last_update timestamp not null default current_timestamp on update current_timestamp,-> primary key(city_id),-> keyidx_fk_country_id(country_id),-> constraint fk_city_country foreign key(country_id) references country(country_id) on delete restrict on update cascade

-> )engine=InnoDB default charset=utf8;

Query OK,0 rows affected (0.04sec)

mysql> desccountry;+-------------+----------------------+------+-----+-------------------+-----------------------------+

| Field | Type | Null | Key | Default | Extra |

+-------------+----------------------+------+-----+-------------------+-----------------------------+

| country_id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |

| country | varchar(50) | NO | | NULL | |

| last_update | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |

+-------------+----------------------+------+-----+-------------------+-----------------------------+

3 rows in set (0.00sec)mysql> insert into country values(1,‘English‘,now());

Query OK, 1 row affected (0.00 sec)

mysql> select * from country;

+------------+---------+---------------------+

| country_id | country | last_update |

+------------+---------+---------------------+

| 1 | English | 2018-10-25 10:34:49 |

+------------+---------+---------------------+

1 row in set (0.00 sec)

mysql> desc city;

+-------------+----------------------+------+-----+-------------------+-----------------------------+

| Field | Type | Null | Key | Default | Extra |

+-------------+----------------------+------+-----+-------------------+-----------------------------+

| city_id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |

| city | varchar(50) | NO | | NULL | |

| country_id | smallint(5) unsigned | NO | MUL | NULL | |

| last_update | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |

+-------------+----------------------+------+-----+-------------------+-----------------------------+

4 rows in set (0.00 sec)mysql> insert into city values(251,‘Kabul‘,1,now());

Query OK,1 row affected (0.00sec)

mysql> select * fromcity;+---------+-------+------------+---------------------+

| city_id | city | country_id | last_update |

+---------+-------+------------+---------------------+

| 251 | Kabul | 1 | 2018-10-25 10:36:51 |

+---------+-------+------------+---------------------+

1 row in set (0.00sec)

上面的“on update current_timestamp”表示更新表的记录的时候 ，也自动更新修改的时间。一句话“在创建新记录和修改现有记录的时候都对这个数据列刷新”。

1、现在我们删除父表中的一个数据记录。

mysql> delete from country where country_id=1;

ERROR1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`exercise`.`city`, CONSTRAINT `fk_city_country` FOREIGN KEY (`country_id`) REFERENCES `country` (`country_id`) ON UPDATE CASCADE)

我们发现是删除不了的，因为字表参照了父表的一个记录(我们在字表参照里面设置了“限制在字表有关联的情况下父表不能删除”---delete  restrict)

2、然后我们更新一个记录看看。

mysql> update country set country_id=251 where country=‘English‘;

ERROR1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY columnmysql> SET SQL_SAFE_UPDATES=0; #我是以安全模式启动的mysql，设置一下就可以了

Query OK,0 rows affected (0.00sec)

mysql> update country set country_id=251 where country=‘English‘;

Query OK,1 row affected (0.01sec)

Rows matched:1 Changed: 1 Warnings: 0mysql> select * fromcountry;+------------+---------+---------------------+

| country_id | country | last_update |

+------------+---------+---------------------+

| 251 | English | 2018-10-25 10:47:01 |

+------------+---------+---------------------+

1 row in set (0.00sec)

mysql> select * fromcity;+---------+-------+------------+---------------------+

| city_id | city | country_id | last_update |

+---------+-------+------------+---------------------+

| 251 | Kabul | 251 | 2018-10-25 10:38:28 |

+---------+-------+------------+---------------------+

1 row in set (0.00 sec)

我们更新的父表的主键记录，发现子表的外键记录也自动更新了，因为子表的外键参照父表的主键。并且子表在约束条件里面写了"update cascade"。

3、现在我们删除父表的主键

mysql> alter table country drop columncountry_id;

ERROR1829 (HY000): Cannot drop column ‘country_id‘: needed in a foreign key constraint ‘fk_city_country‘ of table ‘exercise.city‘

当某个表被其他表创建了外键参照，那么该表的对应索引或者主键禁止被删除。

还有一些需要注意的：

在导入多个表的数据的时候，如果需要忽略表之前的导入顺序，可以暂时关闭外键的检查，在执行load data和alter table操作的时候，可以暂时关闭外键约束来加快处理的速度。关闭的指令是“SET FORENGN_KEY_CHECKS=0”；执行完成之后，通过执行"SET FOREIGN_KEY_CHECKS=1"语句改回原始状态。

原文：https://www.cnblogs.com/FengGeBlog/p/9848530.html