一、外键约束
概念:
外键用来让两个表的数据之间建立链接,保证数据的一致性和完整性。
语法:
(1)添加约束
-- 创建表是添加外键约束
CREATE TABLE 表名(
列名 数据类型,
…
[CONSTRAINT] [外键名称] FOREIGN KEY(外键列名) REFERENCE 主表(主表列名)
);
-- 建完表后添加外键约束
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCE 主表名称(主表列名称)
(2)删除约束
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;
案例
首先创建员工信息表和部门信息表,并在员工表中添加外键。(把员工表当作从表,部门表当作主表),员工表和部门表就是一个多对一的案例
-- 员工表
CREATE TABLE emp(
id int PRIMARY KEY auto_increment, -- id
name VARCHAR(20),-- 姓名
age int, -- 年龄
dep_id int, -- 部门
-- 添加外键dep_id,关联dept表的id主键
CONSTRAINT fk_emp_dept FOREIGN KEY(dep_id) REFERENCES dept(id)
);
-- 部门表
CREATE TABLE dept(
id int PRIMARY KEY auto_increment, -- 部id
dep_name VARCHAR(20), -- 部门名称
addr VARCHAR(20) -- 地址
);
INSERT INTO emp(NAME,age,dep_id) VALUES
('张三',20,1),
('李四',20,1),
('王五',20,1),
('赵六',20,2),
('田七',22,2),
('周八',18,2);
INSERT INTO dept(dep_name,addr) VALUES
('研发部','广州'),
('销售部','深圳');
-- 删除外键
ALTER TABLE emp DROP FOREIGN KEY fk_emp_dept;
按照如下图所示顺序执行sql语句:部门表–>员工表–>部门信息–>员工信息
执行完毕后如下图点击对象选中这两个表,点击鼠标右键,选中‘逆向表到模型’。
可以看到这两个表的模型中间有一条线,这条线代表外键。
此时我们执行删除外键的语句
然后再次执行上述‘逆向表到模型’ 可以看到线消失了,表示我们删除成功。
二、表关系之一对多
1、一对多(多对一)
如:部门和员工,一个部门对应多个员工,一个员工对应一个部门
2、实现方式:在多的一方建立外键,指向一的一方的主键
3、具体实现参照上例,部门和员工。
三、表关系之多对多
1、多对多
如:商品和订单,一个商品对应多个订单,一个订单包含多个商品
2、实现方式
建立第三张中间表,中间表至少包含两个外键,分别关联两方主键
3、实现案例:
-- 订单表
CREATE TABLE tb_order(
id int PRIMARY KEY auto_increment, -- id
payment double(10,2),
payment_type TINYINT,
status TINYINT
);
-- 商品表
CREATE TABLE tb_goods(
id int PRIMARY KEY auto_increment,
title VARCHAR(100),
price DOUBLE(10,2)
);
-- 订单商品中间表
CREATE TABLE tb_order_goods(
id int PRIMARY KEY auto_increment,
order_id int,
goods_id int,
count int
);
-- 添加外键,向订单商品中间表添加两个外键,关联商品表和订单表的主键。
ALTER TABLE tb_order_goods ADD CONSTRAINT fk_order_id FOREIGN KEY(order_id) REFERENCES tb_order(id);
ALTER TABLE tb_order_goods ADD CONSTRAINT fk_goods_id FOREIGN KEY(goods_id) REFERENCES tb_goods(id);
如图执行创建三个表
执行添加外键的语句
再次执行上述‘逆向表到模型’ 具体对应关系如图标注。
四、表关系之一对一
1、一对一
如:用户信息和用户详情信息
一对一关系多用于表拆分,将一个实体中经常使用的字段放一张表,不经常使用的字段放另一张表,用于提升查询能力
2、实现方式:在任意一方加入外键,关联另一方主键,并且设置外键为唯一(UNIQUE)
3、实现案例
-- 创建用户表
CREATE TABLE tb_user(
id int PRIMARY KEY auto_increment,
photo VARCHAR(30),
nickname VARCHAR(20),
age int,
sex char(2),
desc_id int NOT NULL UNIQUE -- 设置关联的用户详情表主键不为空且唯一
);
-- 创建用户详情表
CREATE TABLE tb_user_desc(
id int PRIMARY KEY auto_increment,
city VARCHAR(10),
edu VARCHAR(10),
income DOUBLE(7,2),
status VARCHAR(10)
);
-- 添加用户
INSERT INTO TABLE tb_user(photo,nickname,age,sex,desc_id) VALUES
('a.jpg','一场梦',23,'女',1),
('b.png','风清扬',35,'男',2),
('c.jpg','赵云',41,'男',3),
-- 添加用户详情信息
INSERT INTO TABLE tb_user_desc(city,edu,income,status) VALUES
('广州','硕士',300000,'单身'),
('湖北','本科',100000,'离异'),
('河南','本科',200000,'单身'),
-- 添加外键
ALTER TABLE tb_user ADD CONSTRAINT fk_user_userdesc FOREIGN KEY(desc_id) REFERENCES tb_user_desc(id);
如图执行语句创建用户表和用户详情表
往两张表中添加数据
添加外键
再次执行上述‘逆向表到模型’ 具体对应关系如图。