秋秋小事头像

Python 数据库 约束-PostgreSQL Constraint

        约束(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

文章来源转载

评论

评论列表

微信小程序
QQ小程序

关于作者

点赞数:0
关注数:0
粉丝:0
文章:0
关注标签:0
加入于:--