PostgreSQL之外键约束-CSDN博客

本文链接：https://blog.csdn.net/qq_35462323/article/details/120534375

外键

又称外键约束，Foreign key constraint。

外键是一个表中，用于标识另一张表中行的一个字段或多个字段。包含外键的表称为引用表，外键引用表称为被引用表。所谓外键约束是指引用字段必须在被引用字段中出现。被引用字段需要是唯一约束或主键。

外键约束维护引用表和被引用表之间的参照完整性（referential integrity）。

外键约束可以在创建表时定义，也可以在表创建后通过alter table语句定义。

定义外键约束的完整语法

<span style="color:#000000"><span style="background-color:#ffffff"><code>[ CONSTRAINT constraint_name ] FOREIGN KEY ( column_name [, … ] ) REFERENCES reftable [ ( refcolumn [, … ] ) ] [ MATCH matchtype ] [ ON DELETE action ] [ ON UPDATE action ]
</code></span></span>

其中：
constraint_name : 外键约束名称
( column_name [, … ] ) : 引用表中的引用字段
reftable : 被引用表
( refcolumn [, … ] ) : 被引用表中的被引用字段，和( column_name [, … ] )对应。
MATCH [SIMPLE|FULL] : 外键匹配模式,如果引用字段全部不是NULL,则强匹配,否则根据匹配模式进行弱匹配。

SIMPLE,默认值，只要引用字段中任一字段为NULL，则不要求与被引用字段强匹配；
FULL,只有引用字段全部为NULL，才不要求与被引用字段强匹配。

ON DELETE [CASCADE | NO ACTION] : 默认NO ACTION。

CASCADE,删除被引用表数据时级联删除引用表数据
NO ACTION,删除被引用表数据时必须先删除引用表数据，否则，如果引用表如果存在数据，直接删除被引用表数据返回失败。

ON UPDATE [CASCADE | NO ACTION] : 默认NO ACTION

CASCADE,更新被引用表时级联更新引用表数据
NO ACTION,更新被引用表时必须先删除引用表数据，否则，如果引用表存在数据，直接更新被引用表数据返回失败。

创建表时定义外键约束

创建一张t_currency表

<span style="color:#000000"><span style="background-color:#ffffff"><code>CREATE TABLE t_currency
(
    id      int,
    shortcut    char (3),
    PRIMARY KEY (id)
);
</code></span></span>

创建一张t_product表, 其中包含外键约束currency_id引用t_currency的id字段。pg中定义外键约束需要用到REFERENCES关键字。

<span style="color:#000000"><span style="background-color:#ffffff"><code>CREATE TABLE t_product
(
    id      int,
    name        text,
    currency_id int REFERENCES t_currency (id),
    PRIMARY KEY (id)
);

</code></span></span>

上面提到被引用字段需要时被引用表的主键和唯一约束。如果我们引用了非主键和唯一约束会发生什么？来看一下。创建一张表t_product1,定义外键约束引用t_currency的shortcut，shortcut既不是主键，也不是唯一约束。

<span style="color:#000000"><span style="background-color:#ffffff"><code>postgres=<span style="color:#5c6370"><em># CREATE TABLE t_product1</em></span>
(
    id      int,
    name        text,
    currency_id int REFERENCES t_currency (shortcut),
    PRIMARY KEY (id)
);
ERROR:  there is no unique constraint matching given keys <span style="color:#c678dd">for</span> referenced table <span style="color:#98c379">"t_currency"</span>
</code></span></span>

可以看到创建失败，提示“there is no unique constraint matching given keys for referenced table "t_currency"”

创建完外键约束之后，t_product和t_currency之间的参照完整性就建立了，也就说我不能在t_product中插入一条curruncy_id非空但没有出现在t_currency的记录。

尝试在t_product表中插入一条记录，其中currency_id等于1,此时t_currency表中并没有id等于1的记录。

<span style="color:#000000"><span style="background-color:#ffffff"><code>postgres=<span style="color:#5c6370"><em># INSERT INTO t_product VALUES (1, 'PostgreSQL consulting', 1);</em></span>
ERROR:  insert or update on table <span style="color:#98c379">"t_product"</span> violates foreign key constraint <span style="color:#98c379">"t_product_currency_id_fkey"</span>
DETAIL:  Key (currency_id)=(1) is not present <span style="color:#c678dd">in</span> table <span style="color:#98c379">"t_currency"</span>.
</code></span></span>

可以看到执行报错：

ERROR: insert or update on table "t_product" violates foreign key constraint "t_product_currency_id_fkey" DETAIL: Key (currency_id)=(1) is not present in table "t_currency".

这个点在实践场景是非常需要注意的，因为引用表和被引用表之间的参照完整性的存在，就已经确立了表记录的插入顺序。如果没有外键，则可以以任意顺序插入任何表，但有了外键，就需要保证正确的插入顺序。

定义多个字段组成的外键

创建一张表t_unique, 它包含唯一约束uk_tbl_unique_a_b(a,b)

<span style="color:#000000"><span style="background-color:#ffffff"><code>create table t_unique(
a int not null,
b int,
c varchar(10) not null default <span style="color:#98c379">'catch u'</span>,
constraint uk_tbl_unique_a_b unique(a,b)
);
</code></span></span>

创建一张表t_child，定义外键约束引用t_unique的a,b字段。

<span style="color:#000000"><span style="background-color:#ffffff"><code>CREATE TABLE t_child(
  c1 <span style="color:#e6c07b">integer</span> PRIMARY KEY,
  c2 <span style="color:#e6c07b">integer</span>,
  c3 <span style="color:#e6c07b">integer</span>,
  FOREIGN KEY (c2, c3) REFERENCES t_unique (a, b)
);
</code></span></span>

给已存在的表定义外键

使用 ALTER TABLE 给一个已存在的表定义外键。

示例：

<span style="color:#000000"><span style="background-color:#ffffff"><code>ALTER TABLE t_child 
ADD CONSTRAINT fk_c1 FOREIGN KEY (c1) REFERENCES t_parent (p1);
</code></span></span>

删除外键

示例：

<span style="color:#000000"><span style="background-color:#ffffff"><code>alter table t_child drop constraint fk_c1;
</code></span></span>

禁用外键

有时候我们想让外键暂时失效，而不是删除它，可以怎么做？

<span style="color:#000000"><span style="background-color:#ffffff"><code>alter table t_product <span style="color:#e6c07b">disable</span> trigger all;
</code></span></span>

disable trigger all 会禁用表上的所有外键，同时也禁用负责验证约束的内部触发器。

<span style="color:#000000"><span style="background-color:#ffffff"><code>postgres=<span style="color:#5c6370"><em>#  INSERT INTO t_product VALUES (1, 'PostgreSQL consulting', 1);</em></span>
ERROR:  insert or update on table <span style="color:#98c379">"t_product"</span> violates foreign key constraint <span style="color:#98c379">"t_product_currency_id_fkey"</span>
DETAIL:  Key (currency_id)=(1) is not present <span style="color:#c678dd">in</span> table <span style="color:#98c379">"t_currency"</span>.
postgres=<span style="color:#5c6370"><em># alter table t_product disable trigger all;</em></span>
ALTER TABLE
postgres=<span style="color:#5c6370"><em>#  INSERT INTO t_product VALUES (1, 'PostgreSQL consulting', 1);</em></span>
INSERT 0 1
</code></span></span>

这一点在做数据迁移时也很重要。数据迁移的时，遇到有外键约束的表，如果不注意表数据的导入顺序将会导致数据加载失败。

怎么重新启用外键盘约束？

<span style="color:#000000"><span style="background-color:#ffffff"><code>alter table t_product <span style="color:#e6c07b">enable</span> trigger all;
</code></span></span>

看下有没有生效:

<span style="color:#000000"><span style="background-color:#ffffff"><code>postgres=<span style="color:#5c6370"><em># alter table t_product enable trigger all;</em></span>
ALTER TABLE
postgres=<span style="color:#5c6370"><em>#  INSERT INTO t_product VALUES (2, 'PostgreSQL consulting1', 2);</em></span>
ERROR:  insert or update on table <span style="color:#98c379">"t_product"</span> violates foreign key constraint <span style="color:#98c379">"t_product_currency_id_fkey"</span>
DETAIL:  Key (currency_id)=(2) is not present <span style="color:#c678dd">in</span> table <span style="color:#98c379">"t_currency"</span>.
</code></span></span>

启用之后，可以发现，外键约束已经启用，它会对于后续新插入或者更新的数据会进行检查。

这里有个问题，怎么去验证老的数据呢？

方法：修改pg_constraint表，将convalidated置为false，然后使用 Alter table validate constraint语句。
示例：

<span style="color:#000000"><span style="background-color:#ffffff"><code>postgres=<span style="color:#5c6370"><em># select convalidated from pg_constraint where conname = 't_product_currency_id_fkey';</em></span>
 convalidated
--------------
 t
(1 row)

postgres=<span style="color:#5c6370"><em># update pg_constraint set convalidated = false where conname = 't_product_currency_id_fkey';</em></span>
UPDATE 1
postgres=<span style="color:#5c6370"><em># select convalidated from pg_constraint where conname = 't_product_currency_id_fkey';</em></span>
 convalidated
--------------
 f
(1 row)

postgres=<span style="color:#5c6370"><em># Alter table t_product validate constraint t_product_currency_id_fkey;</em></span>
ERROR:  insert or update on table <span style="color:#98c379">"t_product"</span> violates foreign key constraint <span style="color:#98c379">"t_product_currency_id_fkey"</span>
DETAIL:  Key (currency_id)=(1) is not present <span style="color:#c678dd">in</span> table <span style="color:#98c379">"t_currency"</span>.
</code></span></span>

我们现在知道了对于存在外键约束的表，表数据的插入顺序很重要，对于这一点，cybertec提供了一个魔法SQL，用于查询我们应该插入数据的顺序。

<span style="color:#000000"><span style="background-color:#ffffff"><code>WITH RECURSIVE fkeys AS (
   /* <span style="color:#e6c07b">source</span> and target tables <span style="color:#c678dd">for</span> all foreign keys */
   SELECT conrelid AS <span style="color:#e6c07b">source</span>,
          confrelid AS target
   FROM pg_constraint
   WHERE contype = <span style="color:#98c379">'f'</span>
),
tables AS (
      (   /* all tables ... */
          SELECT oid AS table_name,
                 1 AS level,
                 ARRAY[oid] AS trail,
                 FALSE AS circular
          FROM pg_class
          WHERE relkind = <span style="color:#98c379">'r'</span>
            AND NOT relnamespace::regnamespace::text LIKE ANY
                    (ARRAY[<span style="color:#98c379">'pg_catalog'</span>, <span style="color:#98c379">'information_schema'</span>, <span style="color:#98c379">'pg_temp_%'</span>])
       EXCEPT
          /* ... except the ones that have a foreign key */
          SELECT <span style="color:#e6c07b">source</span>,
                 1,
                 ARRAY[ <span style="color:#e6c07b">source</span> ],
                 FALSE
          FROM fkeys
      )
   UNION ALL
      /* all tables with a foreign key pointing a table <span style="color:#c678dd">in</span> the working <span style="color:#e6c07b">set</span> */
      SELECT fkeys.source,
             tables.level + 1,
             tables.trail || fkeys.source,
             tables.trail @> ARRAY[fkeys.source]
      FROM fkeys
         JOIN tables ON tables.table_name = fkeys.target
      /*
       * Stop when a table appears <span style="color:#c678dd">in</span> the trail the third time.
       * This way, we get the table once with <span style="color:#98c379">"circular = TRUE"</span>.
       */
      WHERE cardinality(array_positions(tables.trail, fkeys.source)) < 2
),
ordered_tables AS (
   /* get the highest level per table */
   SELECT DISTINCT ON (table_name)
          table_name,
          level,
          circular
   FROM tables
   ORDER BY table_name, level DESC
)
SELECT table_name::regclass,
       level
FROM ordered_tables
WHERE NOT circular
ORDER BY level, table_name;
</code></span></span>

输出结果示例：

<span style="color:#000000"><span style="background-color:#ffffff"><code> table_name | level
------------+-------
 t_currency |     1
 t_product  |     2
(2 rows)
</code></span></span>