外键约束

自带的引擎只有innoDB引擎支持外键，外键要求父表必须有对应列的索引，子表会自动创建索引
下面是两个表country国家，字表city

创建国家表

last_update语句的意思是时间戳不为空，默认插入（第三条插入表明以实际插入数值为准）和更新时间为当前时间
primary key 有两个作用，一是约束作用（constraint），用来规范一个存储主键和唯一性，但同时也在此key上建立了一个主键索引

CREATE TABLE country(country_id INT UNSIGNED NOT NULL AUTO_INCREMENT,country VARCHAR(20),last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ONUPDATE CURRENT_TIMESTAMP, PRIMARY KEY(country_id));INSERT INTO country VALUES(1,'china',NOW());insert into country values(2,'japan',curdate());insert into country values(3,'usa','2007-11-20 11:11:50');mysql> select * from country;+------------+---------+---------------------+| country_id | country | last_update |+------------+---------+---------------------+| 1 | china | 2019-07-12 09:30:16 || 2 | japan | 2019-07-12 00:00:00 || 3 | usa | 2007-11-20 11:11:50 |+------------+---------+---------------------+3 rows in set (0.00 sec

创建城市表

KEY idx_fk_coutry_id (country_id) country_id为索引，可以不要，因为创建外键会创建该列的索引
添加外键的语句：创建约束fk_city_country，外键 FOREIGN KEY 所在列是本表country_id，级联REFERENCES的是表country的country_id列，country表不能单独删除，级联更新

CREATE TABLE city(city_id INT UNSIGNED NOT NULL auto_increment,city VARCHAR(20) NOT NULL,last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, country_id INT UNSIGNED NOT NULL,PRIMARY KEY (city_id),KEY idx_fk_coutry_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;INSERT INTO city values(1,'nanjing',NOW(),1);mysql> select * from city;+---------+---------+---------------------+------------+| city_id | city | last_update | country_id |+---------+---------+---------------------+------------+| 1 | nanjing | 2019-07-12 09:32:28 | 1 |+---------+---------+---------------------+------------+1 row in set (0.00 sec)

更新外键和删除操作

#更新countrymysql> update country set country_id=2 where country_id=1;Query OK, 1 row affected (0.05 sec)#更新后查询,city级联更新mysql> select * from country;+------------+---------+---------------------+| country_id | country | last_update |+------------+---------+---------------------+| 2 | japan | 2019-07-12 00:00:00 || 3 | usa | 2007-11-20 11:11:50 || 10 | china | 2019-07-12 09:34:32 |+------------+---------+---------------------+3 rows in set (0.00 sec)mysql> select * from city;+---------+---------+---------------------+------------+| city_id | city | last_update | country_id |+---------+---------+---------------------+------------+| 1 | nanjing | 2019-07-12 09:32:28 | 10 |+---------+---------+---------------------+------------+1 row in set (0.00 sec)#删除citymysql> delete from country where country_id=1;ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`city`, CONSTRAINT `fk_city_country` FOREIGN KEY (`country_id`) REFERENCES `country` (`country_id`) ON DELETE RESTRICT ON UPDATE CA SCADE)