数据库版本:KingbaseES V008R006C008B0014
1. 什么是外键约束
外键约束是关系数据库管理系统中用来维护表与表之间关联性、数据完整性和数据一致性的一种约束。它是一个列或列集合,包含了另一个表中的主键或唯一键的值。
它具有如下特点:
- 维护数据完整性:外键约束只允许在外键列中引用已存在于主键/唯一列中的值,防止出现无效数据的情况。
- 实现约束动作:外键约束可定义一系列约束动作(如级联更新、级联删除、设置空值等),在主表中的记录发生变化时对相关的外键表进行相应的操作,以保持数据关系的一致性。
- 提高操作效率:外键约束可以帮助数据库系统优化数据存储和查询操作,提高数据操作的效率和性能。
2. 外键约束的用法
外键就是将多张表的列关联在一起,但是关联的这些列必须包含主键或唯一键,所以只需要先创建2张表:
--创建第1张表
CREATE TABLE t1(
t1_c1 INT PRIMARY KEY, --关联列
t1_c2 INT,
t1_c3 DATE
);
--创建第2张
CREATE TABLE t2(
t2_c1 INT PRIMARY KEY, --关联列
t2_c2 VARCHAR(50)
);
关联 t1_c1 和 t2_c1 主键列,创建外键
--t1作为子表、t2作为父表
ALTER TABLE t1
ADD CONSTRAINT fk_t1_t2
FOREIGN KEY (t1_c1) REFERENCES t2(t2_c1);
/*
ALTER TABLE 表名
ADD CONSTRAINT 外键名
FOREIGN KEY (当前表列名) REFERENCES 关联主表名(关联主表列名);
*/
外键也可以直接在创建表时添加:
--创建第1张表
CREATE TABLE t1(
t1_c1 INT PRIMARY KEY, --关联列
t1_c2 INT,
t1_c3 DATE
);
--创建第2张表(在需要关联列后面使用关键字REFERENCES指定表名(列名))
CREATE TABLE t2(
t2_c1 INT PRIMARY KEY REFERENCES t1(t1_c1), --创建外键
t2_c2 VARCHAR(50)
);
创建多张外键关联表也是同样的方法,B关联A,C再关联B
-- 创建表 A
CREATE TABLE A (
a_id INT PRIMARY KEY, --关联列
name VARCHAR(50)
);
-- 创建表 B
CREATE TABLE B (
id INT PRIMARY KEY,
b_id INT REFERENCES A(a_id) --关联A表
);
-- 创建表 C
CREATE TABLE C (
id INT PRIMARY KEY,
c_id INT REFERENCES B(id) --关联B表
);
删除外键直接使用alter方法:
ALTER TABLE 表名 DROP CONSTRAINT 外键名;
如果要删除关于外键的表,则需要先删除子表,再删除主表
3. 外键约束的案例
3.1. 部门员工表
编写2张部门与员工的外键关联表
-- 创建部门表 Department
CREATE TABLE Department (
部门编号 INT PRIMARY KEY, --关联列
部门名称 VARCHAR(50)
);
-- 创建员工表 Employee
CREATE TABLE Employee (
员工编号 INT PRIMARY KEY,
员工姓名 VARCHAR(50),
部门编号 INT REFERENCES Department(部门编号) --关联父表
);
创建表后插入数据
--插入部门数据
INSERT INTO department VALUES (1, 'IT部门');
INSERT INTO department VALUES (2, '综合部门');
--插入员工数据
INSERT INTO employee VALUES (1, '小李', 1); -- 小李属于IT部门
INSERT INTO employee VALUES (2, '小张', 1); -- 小张属于IT部门
INSERT INTO employee VALUES (3, '小王', 2); -- 小王属于综合部门
- 两张表的 "部门编号" 是相关联的外键
当存在关联数据后,此时无法删除部门表(父表)中的数据
DELETE FROM department WHERE 部门编号 = 2;
只能先删除员工表(子表)数据,再删除父表数据
区分父表和子表方法也很简单,查看详细信息时写的引用哪张表,那么父表就是哪张表。或者出现Referenced by(由引用)字样就是父表
3.2. 客户订单表
创建3张客户订单的关联表
语句如下:
-- 创建客户表(Customer)
CREATE TABLE Customer (
customer_id SERIAL PRIMARY KEY,
customer_name VARCHAR(50)
);
-- 创建产品表(Product)
CREATE TABLE Product (
product_id SERIAL PRIMARY KEY,
product_name VARCHAR(50),
price NUMERIC(10, 2)
);
-- 创建订单表(Order)
CREATE TABLE Order1 (
order_id SERIAL PRIMARY KEY,
customer_id INT REFERENCES Customer(customer_id), --关联客户表
product_id INT REFERENCES Product(product_id), --关联产品表
quantity INT,
order_date DATE
);
-- 插入客户信息
INSERT INTO Customer (customer_name) VALUES ('小李');
INSERT INTO Customer (customer_name) VALUES ('小王');
-- 插入产品信息
INSERT INTO Product (product_name, price) VALUES ('象棋棋盘', 100.00);
INSERT INTO Product (product_name, price) VALUES ('迷你凳子', 50.00);
-- 插入订单信息
INSERT INTO Order1 (customer_id, product_id, quantity, order_date) VALUES (1, 1, 2, '2000-01-01');
INSERT INTO Order1 (customer_id, product_id, quantity, order_date) VALUES (2, 2, 1, '2000-01-02');
3张表关联跟2张表关联实际上是一样的,修改、删除时只需要注意主表和子表规则就行。