mysql外键约束3种语句_mysql外键约束

外键约束

自带的引擎只有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)

更新外键和删除操作

#更新country

mysql> 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)

#删除city

mysql> 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)