SQL: Data Definition: Constraint

20th August 2020 at 2:19pm
SQL: Data Definition

Check Constraint 不重要

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric CHECK (price > 0),
    discounted_price numeric CHECK (discounted_price > 0),
    CHECK (price > discounted_price)
);

最后一行 CHECK 是一条 table constraint。上面的两个 CHECK 是 column constraint。功能是一样的。

Not-Null Constraints 重要

列默认是 NULL 的。加 NOT NULL 要求它不能为空:

CREATE TABLE products (
    product_no integer NOT NULL,
    name text NOT NULL,
    price numeric
);

Unique Constraints 重要

单列唯一:

CREATE TABLE products (
    product_no integer UNIQUE,
    name text,
    price numeric
);

多列唯一:

CREATE TABLE example (
    a integer,
    b integer,
    c integer,
    UNIQUE (a, c)
);

给 constraint 加上名字:

CREATE TABLE products (
    product_no integer CONSTRAINT must_be_different UNIQUE,
    name text,
    price numeric
);

注意:对于 PG、MySQL 及 SQL 标准来说,NULL 间是互不相等的。意味着如果 unique constraint 中有一列的值可以是 NULL,那可能会有重复的数值出现。

Primary Keys 重要

PRIMARY KEY = UNIQUE + NOT NULL

CREATE TABLE products (
    product_no integer PRIMARY KEY,
    name text,
    price numeric
);

Multi-column primary keys:

CREATE TABLE example (
    a integer,
    b integer,
    c integer,
    PRIMARY KEY (a, c)
);

Foreign Keys 重要

CREATE TABLE products (
    product_no integer PRIMARY KEY,
    name text,
    price numeric
);

CREATE TABLE orders (
    order_id integer PRIMARY KEY,
    product_no integer REFERENCES products (product_no),
    quantity integer
);

REFERENCES 表示外键。products (product_no)(product_no) 可以省略,省略时表示默认使用 products 表的主键。

多列外键,使用 table constraint 来表达:

CREATE TABLE t1 (
  a integer PRIMARY KEY,
  b integer,
  c integer,
  FOREIGN KEY (b, c) REFERENCES other_table (c1, c2)
);

多个外键以实现多对多关系,如下示例的 order_items 表:

CREATE TABLE products (
    product_no integer PRIMARY KEY,
    name text,
    price numeric
);

CREATE TABLE orders (
    order_id integer PRIMARY KEY,
    shipping_address text,
    ...
);

CREATE TABLE order_items (
    product_no integer REFERENCES products,
    order_id integer REFERENCES orders,
    quantity integer,
    PRIMARY KEY (product_no, order_id)
);

外键存在时,对删除的控制:

CREATE TABLE order_items (
    product_no integer REFERENCES products ON DELETE RESTRICT,
    order_id integer REFERENCES orders ON DELETE CASCADE,
    quantity integer,
    PRIMARY KEY (product_no, order_id)
);

其中:

  • 对 product_no 启用的 RESTRICT:表示当 order_items 中还存在某一 product 时,这个 product 无法在 products 表中被删除
  • 对 order_id 启用的 CASCADE:表示当 orders 表中某一 order 被删除时,它相关的 order_items 行也会被删除

此外还有跟 RESTRICT 非常像的 NO ACTION(不深究,使用 RESTRICT 即可);以及 SET DEFAULT 和 SET NULL。后两者表示 referenced row 一旦被删,则 referencing row 中相应的字段设为默认值或者 NULL;比如 product 被删,那么其在 order_items 中有关联的行的值会被设置成默认值或者 NULL。

Referenced column 应该是 PRIMARY KEY 或者 UNIQUE 的。这意味着它是带索引的。Referencing column 不会被自动建索引,但是建议去建,因为 referenced table 中一旦有行被删,数据库需要扫一遍 referencing table 中哪些行引用了它。

Exclusion Constraints 不重要

(略)