Skip to content

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.

动手试一试

常见错误

错误写法

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 个练习

练习 Constraints

查阅不认识的术语

新手友好的术语表用大白话解释编程术语——变量、函数、DOM、Promise 等等。

查看术语表

动手做项目

通过引导式项目应用所学,含起始代码与解决方案。

查看项目

解读错误信息

用大白话解释常见错误——什么意思、为什么发生、如何修复。

查看错误