Constraints
Enforce 数据 integrity 使用主要键, foreign 键, 唯一, 检查, and 不-空.
你将学到
- 为什么 constraints 是 backbone 的数据 integrity
- 主键 and 外键 constraints in 深度
- UNIQUE constraints 到 prevent duplicate 值
- CHECK constraints for 自定义 validation 规则
- NOT NULL and DEFAULT as constraints
概念讲解
为什么约束很重要
Constraints are rules the database enforces on every insert and update. They are the backbone of data integrity — they make it impossible to store invalid data, regardless of which application or user tries. Without constraints, every application that touches the database must independently validate the same rules, and sooner or later one of them will skip a check and corrupt the data.
Primary Key Constraint
A primary key constraint combines NOT NULL and UNIQUE: every row must have a non-null, unique value in the key column(s). Each table can have only one primary key. It is usually an auto-incrementing integer or a UUID. The database creates an index on the primary key automatically, which makes lookups by ID fast.
Foreign Key Constraint
A foreign key constraint ensures that a value in one table references an existing row in another table. FOREIGN KEY (user_id) REFERENCES 用户(id) means you cannot insert an order with a user_id that does not exist. You can also specify what happens on delete: ON DELETE CASCADE (delete dependent rows), ON DELETE SET NULL, or ON DELETE RESTRICT (the default — block the delete).
UNIQUE Constraint
A UNIQUE constraint ensures no two rows share the same value in a column or combination of columns. Unlike a primary key, a table can have multiple UNIQUE constraints, and they allow NULL values (though the exact NULL behavior varies by database). UNIQUE is commonly used for email addresses, usernames, and SKU codes.
CHECK Constraint
A CHECK constraint enforces a custom boolean condition: CHECK (price >= 0), CHECK (状态 IN ('pending', 'shipped', 'delivered')), CHECK (end_date >= start_date). The condition is evaluated on every insert and update. CHECK constraints are powerful but should not contain subqueries or non-deterministic functions.
示例代码
-- Table with every major constraint type
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
total DECIMAL(10, 2) NOT NULL CHECK (total >= 0),
status VARCHAR(20) NOT NULL DEFAULT 'pending'
CHECK (status IN ('pending', 'paid', 'shipped', 'delivered', 'cancelled')),
coupon_code VARCHAR(20) UNIQUE,
created_at TIMESTAMP NOT NULL DEFAULT NOW()
);
-- Add a composite unique constraint
ALTER TABLE order_items
ADD CONSTRAINT unique_product_per_order
UNIQUE (order_id, product_id);
-- Add a check constraint later
ALTER TABLE orders
ADD CONSTRAINT check_paid_has_total
CHECK (status != 'paid' OR total > 0);
表使用 PK, FK 使用 cascade 删除, CHECK on price and 状态, UNIQUE on coupon, plus composite UNIQUE and cross-列 CHECK 添加通过 ALTER.
动手试一试
- SQL Formatter
格式 constraint 定义 in CREATE TABLE.
- JSON Schema Validator
JSON Schema 是 NoSQL equivalent 的 SQL constraints.
常见错误
错误写法
Forgetting ON DELETE behavior for foreign 键
正确做法
通过默认, 删除 referenced 行是 blocked. Decide explicitly: CASCADE for dependent 行, SET NULL for 可选链接, RESTRICT 到块.
错误写法
验证 business 规则仅 in application code
正确做法
Put 基本 integrity 规则 (price >= 0, 状态 in 允许设置) in CHECK constraints. 数据库是 最后行 的 defense 针对坏 数据.
练习 Constraints
7 个练习
查阅不认识的术语
新手友好的术语表用大白话解释编程术语——变量、函数、DOM、Promise 等等。
动手做项目
通过引导式项目应用所学,含起始代码与解决方案。
解读错误信息
用大白话解释常见错误——什么意思、为什么发生、如何修复。