多表操作(外键)

多表操作

外键

外键（foreign key）： 外键为某个表中的一列，它包含另一个表的主键值，定义了两个表之间的关系。

主表（父表）：对于两个具有关联关系的表而言，相关联字段中的主键所在的那个表即是主表。

从表（子表）：对于两个具有关联关系的表而言，相关联字段中的外键所在的那个表即是从表。

外键特点：

• 从表外键的值是对主表主键的引用。

• 从表外键类型，必须与主表主键类型一致。

外键的作用： 保证数据引用的完整性、一致性。

• 要考虑向从表添加数据时，外键字段必须是主表主键的引用，否则添加失败。

• 要考虑从主表中删除数据时，主键并未被从表引用，否则删除失败。

语法

-- 添加外键
alter table 从表 add [constraint][外键名称] foreign key (从表外键字段名) 
references 主表 (主表的主键);
-- [外键名称]用于删除外键约束的，一般建议“_fk”结尾
-- 也可以在建表时添加外键约束，
--CONSTRAINT orders_customers_fk FOREIGN KEY (cust_id) REFERENCES customers (cust_id)

-- 删除外键
alter table 从表 drop foreign key 外键名称
1
2
3
4
5
6
7
8
9

案例

  现在我们有两张表“顾客表”和“订单表”，为了表明订单属于哪个顾客，通常情况下，我们将在订单表上添加一列，用于存放cust_id的信息，此列称为：外键。

# 添加外键
ALTER TABLE orders ADD CONSTRAINT orders_customers_fk FOREIGN KEY (cust_id) REFERENCES customers (cust_id);
# 删除外键
ALTER TABLE orders DROP FOREIGN KEY orders_customers_fk;

# 向主表添加数据
INSERT INTO customers (cust_id,cust_name) VALUES (666,'王老五');-- 成功
# 向从表添加数据
INSERT INTO orders(order_date, cust_id) VALUES (now(),666);-- 成功
# 向从表添加数据
INSERT INTO orders (order_date,cust_id) VALUES (now(),111);-- 失败
# 主表删除数据
DELETE FROM customers WHERE cust_id=666;-- 失败
1
2
3
4
5
6
7
8
9
10
11
12
13

  
  

表关系

  实际开发中，一个项目通常需要很多张表才能完成。例如：一个商城项目就需要顾客表(customers)、商品表(products)、订单表(orders)等多张表。且这些表的数据之间存在一定的关系，接下来我们将在单表的基础上，一起学习多表方面的知识。

一对一关系

• 在实际的开发中不多.因为一对一可以创建成一张表.

• 常见实例：商品表和商品描述表

两种建表原则：

• 外键唯一：主表的主键和从表的外键（唯一），形成主外键关系，外键唯一unique。

• 外键是主键：主表的主键和从表的外键，形成主外键关系。

一种商品只会有一条详细的描述记录

  

一对多关系

• 常见实例：客户和订单，分类和商品，部门和员工, 省份和城市

• 一对多建表原则：在从表(多方)创建一个字段，字段作为外键指向主表(一方)的主键.

一个客户可以有多条购买记录

  

多对多关系

• 常见实例：商品和订单，学生和课程，用户和角色

• 多对多关系建表原则：需要创建第三张表,中间表中至少两个字段，这两个字段分别作为外键指向各自一方的主键

一个订单可能会购买多种商品，一种商品会出现在多个订单中

# 1.添加外键
ALTER TABLE orderitems  ADD CONSTRAINT orderiterms_orders_fk FOREIGN KEY (order_num) REFERENCES orders (order_num);
ALTER TABLE orderitems ADD CONSTRAINT orderiterms_products_fk FOREIGN KEY (prod_id) REFERENCES products(prod_id);
# 2.向订单表插入数据
INSERT INTO orders (order_num, order_date, cust_id) VALUES (101,now(),666);
# 3.向商品表插入数据
INSERT INTO products (prod_id,prod_name,vend_id,prod_price) VALUES (101,'大刀',1002,100);

# 4.向中间表插入一条数据（数据存在）
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price) VALUES (101,1,101,1,100);
# 5.向中间表插入一条数据（数据不存在）  执行失败
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price) VALUES (101,1,102,1,100);-- 产品表不存在102
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price) VALUES (102,1,101,1,100);-- 订单表不存在102
# 6.删除商品表   执行失败
DELETE FROM products WHERE prod_id=101;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

  
  

实战1：省和市

• 方案1：多张表，一对多

-- 创建省份表
create table province(
  pid int PRIMARY KEY,
  pname varchar(32), -- 省份名称
  description varchar(100) -- 描述
);

-- 创建城市表
create table city (
  cid int PRIMARY KEY,
  cname varchar(32), -- 城市名称
  description varchar(100), -- 描述
  province_id int,
  CONSTRAINT city_province_fk foreign key(province_id) references province(pid)
);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

• 方案2：一张表，自关联一对多

create table area (
  id int PRIMARY key AUTO_INCREMENT,
  `name` varchar(32),
  description varchar(100),
  parent_id int,
  CONSTRAINT area_area_fk FOREIGN KEY(parent_id) REFERENCES area(id)
);

INSERT into area values(null, '辽宁省', '这是一个省份', null);
INSERT into area values(null, '大连市', '这是一个城市', 1);
INSERT into area values(null, '沈阳市', '这是一个城市', 1);
INSERT into area values(null, '河北省', '这是一个省份', null);
INSERT into area values(null, '石家庄市', '这是一个城市', 4);
INSERT into area values(null, '保定市', '这是一个城市', 4);
1
2
3
4
5
6
7
8
9
10
11
12
13
14

  
  

实战2：用户和角色

• 多对多关系

-- 用户表
create table `user` (
  uid varchar(32) PRIMARY KEY,
  username varchar(32),
  `password` varchar(32)
);

-- 角色表
create table role (
  rid varchar(32) PRIMARY KEY,
  rname varchar(32)
);

-- 中间表
create table user_role(
  user_id varchar(32),
  role_id varchar(32),
  CONSTRAINT user_role_pk PRIMARY KEY(user_id,role_id),
  CONSTRAINT user_id_fk FOREIGN KEY(user_id) REFERENCES `user`(uid),
  CONSTRAINT role_id_fk FOREIGN KEY(role_id) REFERENCES role(rid)
);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

特别提醒

  现在这种创建外键的方式已经不提倡，甚至被禁止了。因为在维护数据时，每次做DELETE 或者UPDATE都必须考虑外键约束。限制条件太多，效率较低。关联关系通过SQL语句来实现。