mysql 外键_MySQL外键约束

外键约束

1.简介

外键表示一个表中的一个字段被另外一个表中的字段应用.外键对相关表中的数据造成了限制,使MySQL 能够保证参照完整性.

MySQL 中,InnoDB 存储引擎支持外键.在一张表中,可以存在多个外键.

外键的创建可以在创建表的时候创建,也可以在创建表之后增加(考虑数据的完整性问题).

父表:外键所指向的表.

字表:相对于父表,拥有外键的表.

2.语法

create 语法

 create table table_name(column_1,column_2,....constraint constraint_name foreign key (column_name)references parent_table(column_name)on delete actionon update action) engine=InnoDB default charset utf8;
  • constraint 子句允许为外键定义一个名称,如果不写,MySQL 自动生成一个名称

  • foreign key 子句指定子表中要应用父表的列.注意:MySQL 会自动创建一个基于外键的索引.

  • references 子句指定父表中的被引用字段.foreign keyreferences 指定的列数必须相同.

  • on delete: 定义当父表中的记录被删除时,子表的记录应该执行的动作.action包括:

    • on delete restrict:(默认),父表不能删除一个已经被子表引用的记录.

    • on delete no action:等同与on delete restrict

    • on delete cascade: 级联模式,父表删除后,对应子表关联的数据也跟着被删除

    • on delete set null:置空模式,父表删除后,对应子表关联的外键值被设置为NULL,需要注意的是,如果子表的外键设置not null ,则不能使用这种模式,因为会相互冲突.

  • on update:定义父表中的记录更新时,子表的记录应该执行的动作.action 包括:

    • on update restrict:(默认),父表不能更新一个已经被子表引用的记录.

    • on update no action:等同与on delete restrict

    • on update cascade: 级联模式,父表更新后,对应子表关联的数据也跟着被更新

    • on update set null:置空模式,父表更新后,对应子表关联的外键值被设置为NULL,需要注意的是,如果子表的外键设置not null ,则不能使用这种模式.

alter 语法

 -- 添加外键alter table table_name add constraint constraint_name  foreign key column_namereferences parent_table(column_name)on delete actionon update action-- 删除外键alter table table_name drop constraint_name;-- 如果没有显式的定义名字,可以使用如下命令获取show create table table_name;

3.演示

构造两张表categoryesproducts.每个类别有多种产品,而每个产品只属于一个类别.

 -- 设置 类别表 categoryes 和产品表 productscreate table categoryes(    c_id int not null auto_increment,c_name varchar(45) not null,c_description text,primary key (c_id)) engine=InnoDB default charset utf8 comment '类别表';create table products(    p_id int not null auto_increment,p_name varchar(45) not null,p_price decimal(8,4),c_id int,primary key (p_id),constraint fk_products_categoryesforeign key (c_id)references categoryes(c_id)on delete set nullon update cascade) engine=InnoDB default charset utf8 comment '产品表';

在这两张表的基础上,新生成一张vendors 供应商表,并更新products字段

 -- 新生成一张表 供应商 vendors ,并为 products 新添加字段 v_id 外键-- 引用 vendors.v_idcreate table vendors(    v_id int not null auto_increment,v_name varchar(45),primary key (v_id)) engine=InnoDB default charset utf8 comment '供应商';alter table products add column v_id int not null;alter table products addconstraint fk_products_vendors foreign key (v_id)references vendors(v_id)on delete no actionon update cascade;

产看表products 的构成

 mysql root@192.168.101:test> show create table products;                                                                                                                                                          +----------+-----------------------------------------------------------------------------------------------------------------------------------+| Table    | Create Table                                                                                                                      |+----------+-----------------------------------------------------------------------------------------------------------------------------------+| products | CREATE TABLE `products` (                                                                                                         ||          |   `p_id` int(11) NOT NULL AUTO_INCREMENT,                                                                                         ||          |   `p_name` varchar(45) NOT NULL,                                                                                                  ||          |   `p_price` decimal(8,4) DEFAULT NULL,                                                                                            ||          |   `c_id` int(11) DEFAULT NULL,                                                                                                    ||          |   `v_id` int(11) NOT NULL,                                                                                                        ||          |   PRIMARY KEY (`p_id`),                                                                                                           ||          |   KEY `fk_products_categoryes` (`c_id`),                                                                                          ||          |   KEY `fk_products_vendors` (`v_id`),                                                                                             ||          |   CONSTRAINT `fk_products_categoryes` FOREIGN KEY (`c_id`) REFERENCES `categoryes` (`c_id`) ON DELETE SET NULL ON UPDATE CASCADE, ||          |   CONSTRAINT `fk_products_vendors` FOREIGN KEY (`v_id`) REFERENCES `vendors` (`v_id`) ON DELETE NO ACTION ON UPDATE CASCADE       ||          | ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='产品表'                                                                             |+----------+-----------------------------------------------------------------------------------------------------------------------------------+

可以看到它有两个外键,并且他们都有各自的约束条件.

添加数据:

 -- 插入数据insert into categoryes values (1, 'fruit', '水果'),(2, 'book', '书籍');insert into vendors values (1, 'USA'),(2, 'CHINA');insert into products values(1,'APPLE', 1000.0001, 1, 1),(2, 'BANANY', 1000.0001, 1, 2);

现在一个一个来验证他们的约束条件:

  • categeryes 中删除c_id=1 的行,应该得到products.c_id 的值为NULL

  • vendors 中更新v_id=1 的值为3,应该得到products.v_id 的值为3

查看原始数据

 mysql root@192.168.101:test> select * from categoryes;                                                                    +------+--------+---------------+| c_id | c_name | c_description |+------+--------+---------------+| 1    | fruit  | 水果          || 2    | book   | 书籍          |+------+--------+---------------+mysql root@192.168.101:test> select * from products;                                                                              +------+--------+-----------+------+------+| p_id | p_name | p_price   | c_id | v_id |+------+--------+-----------+------+------+| 1    | APPLE  | 1000.0001 | 1    | 1    || 2    | BANANY | 1000.0001 | 1    | 2    |+------+--------+-----------+------+------+mysql root@192.168.101:test> select * from vendors;                                                                          +------+--------+| v_id | v_name |+------+--------+| 1    | USA    || 2    | CHINA  |+------+--------+

执行删除语句

 mysql root@192.168.101:test> delete from categoryes where c_id=1;                                                                        mysql root@192.168.101:test> select * from categoryes;+------+--------+---------------+| c_id | c_name | c_description |+------+--------+---------------+| 2    | book   | 书籍          |+------+--------+---------------+mysql root@192.168.101:test> select * from products;                                              +------+--------+-----------+--------+------+| p_id | p_name | p_price   | c_id   | v_id |+------+--------+-----------+--------+------+| 1    | APPLE  | 1000.0001 | <null> | 1    || 2    | BANANY | 1000.0001 | <null> | 2    |+------+--------+-----------+--------+------+

执行更新语句

 mysql root@192.168.101:test> update vendors set v_id=3 where v_id=1;                                                                        mysql root@192.168.101:test> select * from vendors;                          +------+--------+| v_id | v_name |+------+--------+| 2    | CHINA  || 3    | USA    |+------+--------+mysql root@192.168.101:test> select * from products;                                          +------+--------+-----------+--------+------+| p_id | p_name | p_price   | c_id   | v_id |+------+--------+-----------+--------+------+| 1    | APPLE  | 1000.0001 | <null> | 3    || 2    | BANANY | 1000.0001 | <null> | 2    |+------+--------+-----------+--------+------+

4.MySQL 禁用外键检查

有时候,需要禁用外键检查来完成某种操作,比如将CSV 文件中的数据导入表.

可以使用如下语句

 -- 禁用set  foreign_key_check=0;-- 开启set  foreign_key_check=1;

f9ccd2906830b5e91a8c6dc58333646e.png

表情包
插入表情
评论将由博主筛选后显示,对所有人可见 | 还能输入1000个字符
相关推荐
©️2020 CSDN 皮肤主题: 游动-白 设计师:白松林 返回首页