PostgreSQL — 外键关联操作

Myth丶恋晨 2023-10-05 11:15 25阅读 0赞

## 目录 ##

#### 文章目录 ####

*  目录
 *  PostgreSQL 的外键关联
 *  创建表时定义外键（References，参照）
 *  修改原有表的外键约束
 *  删除外键约束
 *  参考文档

## PostgreSQL 的外键关联 ##

一个外键约束指定一列（或一组列）中的值必须匹配出现在另一个表中某些行的值。我们说这维持了两个关联表之间的引用完整性。注意，一个从表外键所引用的主表 Column 必须是一个主键或者是被唯一约束所限制的。这意味着主表被引用的列总是拥有一个索引（位于主键或唯一约束之下的索引），因此在其上进行的一个引用行是否匹配的检查将会很高效。

## 创建表时定义外键（References，参照） ##

# 主表
    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
    );
    
    # 从表简写，因为缺少声明主表的列名，所以将主表的主键作为引用。
    CREATE TABLE orders (
        order_id integer PRIMARY KEY,
        product_no integer REFERENCES products,
        quantity integer
    );
    
    # 定义多个 Column 组成的外键，要求被约束列（外键）的数量和类型应该匹配被引用列（主键）的数量和类型。
    CREATE TABLE t1 (
      a integer PRIMARY KEY,
      b integer,
      c integer,
      FOREIGN KEY (b, c) REFERENCES other_table (c1, c2)
    );
    
    
    # many2many，添加一个中间表 order_items 来引用两个主表 products 和 orders。
    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)
    );
    
    # on delete/update 的联动操作类型，例如：限制删除/更新、级联删除/更新。
    # RESTRICT 和 NO ACTION 具有相同的语义，两者本质不同在于 NO ACTION 允许关联检查推迟到事务的最后，而 RESTRICT 则会马上报错。
    # SET NULL 主表在 on delete/update 时，设置从表外键为 NULL。
    # SET DEFAULT 主表在 on delete/update 时，设置从表外键为 DEFAULT（默认值）。
    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 ON DELETE RESTRICT, # 限制删除
        order_id integer REFERENCES orders ON DELETE CASCADE,      # 级联删除
        quantity integer,
        PRIMARY KEY (product_no, order_id)
    );

## 修改原有表的外键约束 ##

格式：

ALTER TABLE 从表名 ADD CONSTRAINT 外键约束名 FOREIGN KEY (从表的外键) REFERENCES 主表名 (主表的主键);

注意：如果要给一个已存在的表添加 ON DELETE CASCADE 的外键约束，需要如下步骤：

1.  删除已存在的外键约束。
2.  添加一个 ON DELETE CASCADE 的外键约束。

## 删除外键约束 ##

格式：

alter table 从表名 drop constraint 从表的外键约束名;

## 参考文档 ##

http://www.postgres.cn/docs/12/ddl-constraints.html\#DDL-CONSTRAINTS-FK