创建表1

mysql> CREATE TABLE C( -> -> id TINYINT PRIMARY KEY auto_increment, -> name VARCHAR (20), -> age INT , -> is_marriged boolean -- show create table ClassCharger: tinyint(1) -> -> );

添加表1数据

mysql> INSERT INTO C (name,age,is_marriged) VALUES ("冰冰",12,0), -> ("丹丹",14,0), -> ("歪歪",22,0), -> ("姗姗",20,0), -> ("小雨",21,0);

创建表2  并把子键和主键建立关联并添加级联删除

mysql> CREATE TABLE S3( -> id INT PRIMARY KEY auto_increment, -> name VARCHAR (20), -> charger_id TINYINT, -> FOREIGN KEY (charger_id) REFERENCES C(id) ON DELETE CASCADE  -> );

添加表2数据

mysql> INSERT INTO S3(name,charger_id) VALUES ("alvi1",2), -> ("alvi2",4), -> ("alvi3",5), -> ("alvi4",3), -> ("alvi5",5), -> ("alvi6",3), -> ("alvi7",2);

未删除前数据

mysql> SELECT * from S3;+----+-------+------------+| id | name | charger_id |+----+-------+------------+| 1 | alvi1 | 2 || 2 | alvi2 | 4 || 3 | alvi3 | 5 || 4 | alvi4 | 3 || 5 | alvi5 | 5 || 6 | alvi6 | 3 || 7 | alvi7 | 2 |+----+-------+------------+

mysql> select * from C;+----+------+------+-------------+| id | name | age | is_marriged |+----+------+------+-------------+| 1 | 冰冰 | 12 | 0 || 2 | 丹丹 | 14 | 0 || 3 | 歪歪 | 22 | 0 || 4 | 姗姗 | 20 | 0 || 5 | 小雨 | 21 | 0 |+----+------+------+-------------+

输入删除语句

mysql> delete from C where id = 4;

删除后表数据

mysql> select * from s3;+----+-------+------------+| id | name | charger_id |+----+-------+------------+| 1 | alvi1 | 2 || 3 | alvi3 | 5 || 4 | alvi4 | 3 || 5 | alvi5 | 5 || 6 | alvi6 | 3 || 7 | alvi7 | 2 |+----+-------+------------+6 rows in set (0.00 sec)

mysql> select * from C;+----+------+------+-------------+| id | name | age | is_marriged |+----+------+------+-------------+| 1 | 冰冰 | 12 | 0 || 2 | 丹丹 | 14 | 0 || 3 | 歪歪 | 22 | 0 || 5 | 小雨 | 21 | 0 |+----+------+------+-------------+

------set null方式 在父表上update/delete记录时，将子表上匹配记录的列设为null -- 要注意子表的外键列不能为not null

 先删除之前在s3的外键

mysql> ALTER TABLE s3 DROP FOREIGN KEY s3_ibfk_1;

添加set null方式

mysql> alter table s3 add constraint s3_fk_cc foreign key (charger_id) -> references c(id) on delete set null;

查看表记录

mysql> select * from s3;+----+-------+------------+| id | name | charger_id |+----+-------+------------+| 1 | alvi1 | 2 || 3 | alvi3 | 5 || 4 | alvi4 | 3 || 5 | alvi5 | 5 || 6 | alvi6 | 3 || 7 | alvi7 | 2 |+----+-------+------------+

mysql> select * from C;+----+------+------+-------------+| id | name | age | is_marriged |+----+------+------+-------------+| 1 | 冰冰 | 12 | 0 || 2 | 丹丹 | 14 | 0 || 3 | 歪歪 | 22 | 0 || 5 | 小雨 | 21 | 0 |+----+------+------+-------------+

删除c表jilu

mysql> delete from C where id = 3;

s3表记录变为null

mysql> select * from s3;+----+-------+------------+| id | name | charger_id |+----+-------+------------+| 1 | alvi1 | 2 || 3 | alvi3 | 5 || 4 | alvi4 | NULL || 5 | alvi5 | 5 || 6 | alvi6 | NULL || 7 | alvi7 | 2 |+----+-------+------------+

------Restrict方式 :拒绝对父表进行删除更新操作(了解)------No action方式 在mysql中同Restrict,如果子表中有匹配的记录,则不允许对父表对应候选键 -- 进行update/delete操作（了解）