多表操作(外键)

大家好，欢迎来到IT知识分享网。

多表操作

外键

外键（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 外键名称 

案例

  现在我们有两张表“顾客表”和“订单表”，为了表明订单属于哪个顾客，通常情况下，我们将在订单表上添加一列，用于存放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;-- 失败 

表关系

  实际开发中，一个项目通常需要很多张表才能完成。例如：一个商城项目就需要顾客表(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：省和市

• 方案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) ); 

• 方案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); 

实战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) ); 

特别提醒

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