第13节 用约束保证数据正确

你已经会存数据,但现在数据库允许“编号重复、价格为负”之类问题。这节把正确性规则交给数据库检查,叫作完整性约束。

1 先给Book增加主键

编号应该唯一且有值。目前BookId已是NOT NULL,但还没有阻止重复。确认Book仍只有原来的6行,再执行:

-- 先找重复编号;正常情况下没有结果
SELECT BookId, COUNT(*) AS [重复次数]
FROM dbo.Book GROUP BY BookId HAVING COUNT(*) > 1;
 
-- 首次执行:指定BookId为主键,以后相同编号不能再次插入
ALTER TABLE dbo.Book
ADD CONSTRAINT PK_Book PRIMARY KEY (BookId);

CONSTRAINT PK_Book给规则起名字,以后查错误、改约束都能找到它。主键不是必须叫Id,而是你选择的记录标识。一张表只有一个主键约束,但可以由多列共同组成。

2 在小表中观察其他规则

-- 这个临时表独立演示自增、唯一、检查、默认,不改变6本图书
CREATE TABLE #RuleDemo
(
    Id int IDENTITY(1,1) PRIMARY KEY, -- 自动从1分配编号,主键强制唯一非空
    Name nvarchar(20) NOT NULL,      -- 名称必须有值
    Code varchar(10) NULL UNIQUE,    -- 代码不可重复;单列UNIQUE通常最多一个NULL
    Score int NULL CHECK (Score BETWEEN 0 AND 100),
    CreatedAt datetime NOT NULL DEFAULT GETDATE()
);
 
-- 没写Id,由IDENTITY生成;没写CreatedAt,由DEFAULT填写当前时间
-- 显式Score=NULL表示尚无分数,范围CHECK不会拒绝它
INSERT INTO #RuleDemo(Name,Code,Score)
VALUES(N'样本甲','A01',NULL),(N'样本乙','A02',85);
SELECT * FROM #RuleDemo;
DROP TABLE #RuleDemo;

IDENTITY是自动分配值的属性,不是天然的主键。若不另设PRIMARY KEY或UNIQUE,不能只靠IDENTITY宣称有唯一性约束。自增可能因失败或回滚留下缺口,不保证连续。

3 把六种规则区分清楚

规则解决什么重要边界
PRIMARY KEY标识每条记录唯一、非NULL,可由多列组成
NOT NULL必须填值空字符串仍不是NULL
UNIQUE候选值不能重复不等于只能有一个唯一约束
CHECK满足取值条件只拒绝FALSE,NULL造成的UNKNOWN可以通过
DEFAULT没填写时用什么值显式写NULL时不自动补默认值
FOREIGN KEY引用其他表的值必须有效下一节用真实的分类关系演示

实体完整性由主键等保证;参照完整性由外键保证;用户定义完整性覆盖价格范围等业务规则。不要把这三个分类记成三种不同的查询语法。

4 给图书价格加范围约束

-- WITH CHECK检查已有的6本书也满足条件;以后新写入的价格同样被检查
ALTER TABLE dbo.Book WITH CHECK
ADD CONSTRAINT CK_Book_Price CHECK (Price >= 0);
 
-- 查看检查约束的名字、条件与启用状态,is_disabled应为0
SELECT name, definition, is_disabled, is_not_trusted
FROM sys.check_constraints
WHERE parent_object_id = OBJECT_ID(N'dbo.Book');

删除规则的语法是 ALTER TABLE dbo.Book DROP CONSTRAINT CK_Book_Price;。这是删除约束,不是删除表;课程后续需要这个规则,所以现在不要删除。

本节练习

为什么主键不能重复?NULL分数能否通过0—100的CHECK?若INSERT显式填写CreatedAt=NULL,DEFAULT会自动替代吗?


上一节:12 修改表结构与查看对象 | 下一节:14 建立分类表与外键关系 | 课程目录