约束(Constraint)是数据库设计中保证数据质量的核心机制
约束(Constraint):约束是一套施加在表上的规则,它让数据库自动拒绝任何不符合业务逻辑的数据写入
约束可以在 CREATE TABLE 时定义,也可以在表创建后通过 ALTER TABLE 添加
一 PostgreSQL Constraint
| 约束类型 | 约束的作用 |
| NOT NULL非空约束 |
禁止列中缺失值 |
|
UNIQUE唯一约束 |
禁止重复值 |
|
PRIMARY KEY主键约束 |
组合NOT NULL和UNIQUE,给每行它的公民标识符,每一行只有一个主键 |
|
FOREIGN KEY外键约束 | 引用完整性,把一列绑定到另一张表中的值 |
|
CHECK 检查约束 |
对每一行必须满足的布尔表达式求值,比如价格为正或年龄在合理范围内 |
|
EXCLUSION排他约束 |
把唯一性推广到相等之外的操作符,PostgreSQL特有 |
二 使用
1 NOT NULL
注:它不是零、空字符串、空格的同义词,NULL指的是缺失,NULL被视为不等于任何东西,甚至另一个NULL
给一张已经存在的表添加一个新的列并设置NOT NULL,因为原有的行会多出一列的值而且肯定是值空缺,此时需要给原有的行设置一个默认值,否则会添加失败,比如
ALTER TABLE members ADD COLUMN email TEXT NOT NULL DEFAULT 'pending@example.com'
ALTER TABLE members ALTER COLUMN email DROP NOT NULL,ALTER COLUMN email SET NUT NULL
2 UNIQUE唯一约束
被约束的列中不得有两行持有相同的值,声明“email TEXT UNIQUE”时,Postgre会在该列上创建一个唯一的btree索引,并拒绝产生重复的INSERT或UPDATA,而唯一索引正是让检查变快的原因,UNIQUE约束常与触发器一起联用
复合UNIQUE约束对元组作用相同:‘UNIQUE (user_id, product_id)’,允许多行持有相同的user_id,或product_id,但不允许多行持有相同的use_id和product_id,复合唯一键正是让'同一用户不能给同一产品打两次分,或同一时间房间不能双订'
id为主键,token不可重复并且视多个NULL不等,handle不可重复且视多个NULL相等
CREATE TABLE profile {
id SERIAL PRIMARY KEY,
token TEXT UNIQUE,
handle TEXT UNIQUE NULLS NOT DISTINCT
}
3 PRIMARY KEY主键约束
主键=UNIQUE+NOT NULL,主键、UNIQUE或UNIQUE NOT NULL都可以作为候选的外键,复合主键类似复合UNIQUE
4 FOREIGN KEY外键约束
5 CHECK 检查约束
只能使用当前行上的不可变、确定的函数和操作符,你可以使用算术、字符串函数、CASE以及行内列之间的比较,还有仅凭一行就能求值的数据依赖表达式
不能使用random()、now()、current_user、序列函数,因为一个在CHECK和提交时真值可能改变的约束毫无意义
6 EXCLUSION排他约束
作用于重叠与非等值的唯一性
二 小栗子
PostgreSQL+SQLAIChemy
#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
SQLAlchemy 2.0 + PostgreSQL 六种约束完整示例
=============================================
依赖: pip install sqlalchemy psycopg2-binary
六种约束与 SQLAlchemy 写法的对应关系:
1. NOT NULL -> mapped_column(..., nullable=False)
2. UNIQUE -> mapped_column(..., unique=True)
3. PRIMARY KEY -> mapped_column(..., primary_key=True) (PG 上是 SERIAL)
4. FOREIGN KEY -> mapped_column(ForeignKey("t.c", ondelete="..."))
5. CHECK -> __table_args__ = (CheckConstraint("...", name="..."),)
6. EXCLUSION -> __table_args__ = (ExcludeConstraint((...),(...), using="gist"),) # PG 专有
"""
下面是SQLAIChemy
#!/usr/bin/env python3
# -*- coding: utf-8 -*-
from decimal import Decimal
from sqlalchemy import (
create_engine, text, Integer, String, Numeric,
CheckConstraint, ForeignKey,
)
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column
from sqlalchemy.dialects.postgresql import TSRANGE, ExcludeConstraint
DB_URL = "postgresql+psycopg2://postgres:postgres@localhost:5432/postgres"
class Base(DeclarativeBase):
pass
class Booking(Base):
__tablename__ = "bookings"
__table_args__ = (
CheckConstraint("price > 0", name="chk_price"), # ⑤ CHECK
ExcludeConstraint( # ⑥ EXCLUSION
("room_id", "="),
("during", "&&"),
using="gist",
name="excl_room_time",
),
)
id: Mapped[int] = mapped_column(Integer, primary_key=True) # ① PK
title: Mapped[str] = mapped_column(String(50), nullable=False) # ② NOT NULL
code: Mapped[str] = mapped_column(String(10), unique=True, nullable=False) # ③ UNIQUE
parent_id: Mapped[int | None] = mapped_column(ForeignKey("bookings.id")) # ④ FK (自引用)
price: Mapped[Decimal] = mapped_column(Numeric(10, 2))
room_id: Mapped[int] = mapped_column(Integer, nullable=False)
during: Mapped[str] = mapped_column(TSRANGE, nullable=False)
if __name__ == "__main__":
engine = create_engine(DB_URL, future=True)
with engine.begin() as conn:
conn.execute(text("CREATE EXTENSION IF NOT EXISTS btree_gist"))
Base.metadata.drop_all(engine)
Base.metadata.create_all(engine)
print("✅ bookings 建表完成")
等价的原生DDL
CREATE EXTENSION IF NOT EXISTS btree_gist; -- 排他约束中 room_id 用 = 需依赖它
CREATE TABLE bookings (
id SERIAL PRIMARY KEY, -- ① PK
title VARCHAR(50) NOT NULL, -- ② NOT NULL
code VARCHAR(10) NOT NULL UNIQUE, -- ③ UNIQUE
parent_id INTEGER REFERENCES bookings(id), -- ④ FK(自引用)
price NUMERIC(10,2) CONSTRAINT chk_price CHECK (price > 0), -- ⑤ CHECK
room_id INTEGER NOT NULL,
during TSRANGE NOT NULL,
CONSTRAINT excl_room_time EXCLUDE USING gist ( -- ⑥ EXCLUSION
room_id WITH =,
during WITH &&
)
);
注:理论是自己总结的,代码是AI写的
转载自 CSDN-专业IT技术社区
原文链接:https://blog.csdn.net/jiaoyangdetian/article/details/171111166



