Skip to content

Constraint

Constraint来定于约束，他能够规定特定列的取值范围。

约束级别

从表级别来说，约束分为字段级别和表级别。其中字段级别的也能够使用表级别的改写。在 SQLAlchemy 中他们分别在 Column 的属性或者 Table 的属性中定义:

Python
from sqlalchemy import UniqueConstraint

metadata_obj = MetaData()
mytable = Table(
    "mytable",
    metadata_obj,
    # column 级别 unique 约束
    Column("col1", Integer, unique=True),
    Column("col2", Integer),
    Column("col3", Integer),
    # table 级别 unique 约束
    UniqueConstraint("col2", "col3", name="uix_1"),
)

Note

字段级别约束就是传入参数即可，例如为 unique 参数赋值，而表级别约束需要构建一个约束对象，对象名通常是 [约束名称]Constraint

约束类型

常用的约束包括:

  • 主键约束(PRIMARY KEY)
  • 唯一约束(UNIQUE)
  • 检查约束(CHECK)
  • 外键约束(FOREIGN KEY)

主键约束

Python
from sqlalchemy import PrimaryKeyConstraint

# 字段级别
composite = Table(
    "composite",
    metadata_obj,
    Column("id", Integer, primary_key=True),
    Column("rev_id", Integer),
    Column("note_id", Integer),

# 表级别
my_table = Table(
    "mytable",
    metadata_obj,
    Column("id", Integer),
    Column("version_id", Integer),
    Column("data", String(50)),
    PrimaryKeyConstraint("id", "version_id", name="mytable_pk"),
)

检查约束

Python
from sqlalchemy import CheckConstraint

metadata_obj = MetaData()
mytable = Table(
    "mytable",
    metadata_obj,
    # per-column CHECK constraint
    Column("col1", Integer, CheckConstraint("col1>5")),
    Column("col2", Integer),
    Column("col3", Integer),
    # table level CHECK constraint.  'name' is optional.
    CheckConstraint("col2 > col3 + 5", name="check1"),
)

mytable.create(engine)
CREATE TABLE mytable (
    col1 INTEGER  CHECK (col1>5),
    col2 INTEGER,
    col3 INTEGER,
    CONSTRAINT check1  CHECK (col2 > col3 + 5)
)

唯一约束

Python
from sqlalchemy import UniqueConstraint

metadata_obj = MetaData()
mytable = Table(
    "mytable",
    metadata_obj,
    # per-column anonymous unique constraint
    Column("col1", Integer, unique=True),
    Column("col2", Integer),
    Column("col3", Integer),
    # explicit/composite unique constraint.  'name' is optional.
    UniqueConstraint("col2", "col3", name="uix_1"),
)

外键约束

Python
# 字段级别
user_preference = Table(
    "user_preference",
    metadata_obj,
    Column("pref_id", Integer, primary_key=True),
    Column("user_id", Integer, ForeignKey("user.user_id"), nullable=False),
    Column("pref_name", String(40), nullable=False),
    Column("pref_value", String(100)),
)

# 表级别
invoice_item = Table(
    "invoice_item",
    metadata_obj,
    Column("item_id", Integer, primary_key=True),
    Column("item_name", String(60), nullable=False),
    Column("invoice_id", Integer, nullable=False),
    Column("ref_num", Integer, nullable=False),
    ForeignKeyConstraint(
        ["invoice_id", "ref_num"], ["invoice.invoice_id", "invoice.ref_num"]
    ),
)