第14节 建立分类表与外键关系
到现在Book一直是一张表。书店想按“计算机、文学、科普”分类,如果每本书都重复写长分类名,改名要改很多行。我们把分类独立成表,Book只保存分类编号。
1 先创建被引用的分类表
-- 分类编号唯一,一种分类只保存一次名称
CREATE TABLE dbo.Category
(
CategoryId int NOT NULL CONSTRAINT PK_Category PRIMARY KEY,
CategoryName nvarchar(20) NOT NULL
);
INSERT INTO dbo.Category(CategoryId,CategoryName)
VALUES(1,N'计算机'),(2,N'文学'),(3,N'科普');2 再给图书增加分类编号
-- 新列允许NULL,表示书的分类尚未确定
ALTER TABLE dbo.Book ADD CategoryId int NULL;
GO
-- 新增列后先GO分批,再在新批引用该列,避免SQL Server编译时找不到新列
-- 用前面学过的UPDATE把原有图书分到两类
UPDATE dbo.Book SET CategoryId=1 WHERE BookId IN (1,2,3);
UPDATE dbo.Book SET CategoryId=2 WHERE BookId IN (4,5);
-- 星空漫游的CategoryId暂时保留NULL,用来练习未匹配情况
-- 外键:Book中的非NULL分类编号,必须已存在于Category
ALTER TABLE dbo.Book WITH CHECK
ADD CONSTRAINT FK_Book_Category FOREIGN KEY (CategoryId)
REFERENCES dbo.Category(CategoryId);分类表是父表,图书表是子表。“计算机”能对应三本书,一本书最多属于一个分类,叫一对多。外键放在多的一侧Book。
外键不自动表示“必须分类”,因为CategoryId仍允许NULL。如果业务要求每本书必须分类,还需NOT NULL。
3 再引入销售表
同一本书可能多次卖出,不能在Book里只加一个“本次销售数量”就覆盖历史。每一次销售存一行Sale,由BookId说明卖了哪种书。
CREATE TABLE dbo.Sale
(
SaleId int IDENTITY(1,1) NOT NULL CONSTRAINT PK_Sale PRIMARY KEY,
BookId int NOT NULL,
Quantity int NOT NULL CONSTRAINT CK_Sale_Quantity CHECK(Quantity>0),
SaleDate datetime NOT NULL,
CONSTRAINT FK_Sale_Book FOREIGN KEY(BookId) REFERENCES dbo.Book(BookId)
);
-- 不写SaleId,由IDENTITY生成;每一行是一次销售,不是一种图书
INSERT INTO dbo.Sale(BookId,Quantity,SaleDate) VALUES
(1,2,'20260901'),(1,1,'20260902'),
(2,3,'20260902'),(4,2,'20260903');现在有三张表:Category 3行、Book 6行、Sale 4行。编号1的SQL入门有两次销售,编号3、5、6没有销售记录。
4 顺序为什么重要
- 建表时父表的被引用键应先存在,再创建外键。
- 插入子表前,父表中应先有目标记录。
- 删除被引用的父行时,默认会因外键拒绝;按业务先处理子行,不要随意禁用外键。
- 题目没设置级联行为,就不能假设删图书会自动删销售记录。
本节练习
用SELECT分别查看三张表。解释:Sale能否写BookId=99?Book能否写CategoryId=NULL?删除BookId=1为什么会失败?
对照答案
99号图书不存在,Sale外键会拒绝。Book.CategoryId允许NULL,所以可以暂未分类。1号图书被Sale两行引用,默认不能直接删除。
执行提示:这一节改变了学习环境,只初始化一次。后面查询假设分类和销售数据已完成。
上一节:13 用约束保证数据正确 | 下一节:15 用内连接组合两张表的信息 | 课程目录