第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会自动替代吗?
对照答案
主键用来唯一标识记录;CHECK只拒绝FALSE,因此可空分数的NULL能通过;DEFAULT不替换显式NULL,CreatedAt又要求NOT NULL,所以该插入会失败。
上一节:12 修改表结构与查看对象 | 下一节:14 建立分类表与外键关系 | 课程目录