PostgreSQL外键约束-MemFireDB

敏博科技

让数据价值无限可能

什么是外键？

外键（FOREIGN KEY）用于与另一张表关联，列(或一组列)中的值必须匹配另一个表的某一行中出现的值，用于保持数据的一致性。

一个外键约束指定一列（或一组列）中的值必须匹配出现在另一个表中某些行的值。我们说这维持了两个关联表之间的 引用完整性 。

设计外键的目的

1、简化数据表设计，避免数据过于冗余

想要设计如下一张表同时包括学生基本信息、学生考试成绩，涉及十几甚至二十多个字段，颇为复杂。换一种思考方式，如果设计为学生信息表、成绩表，看着会更相对简介一些呢？如何保证两张表之间可以关联呢，从图可以看出是通过学号进行连接的，可数据库是怎么做到的呢？这就是外键的作用了。

2、保持数据的一致性与完整性

在不设置外键的情况下，数据库认为学生信息表与成绩表是没有关联的。假如在成绩表中插入一条记录(例如：201909260004)，但这个值在学生信息表中是没有的。由于设置外键，数据库不会做关联检查，是允许该插入操作的。此时的结果：有个学生不在学生信息表中，查不到该学生相关信息，但有成绩。

当设置外键时，插入成绩表的值必须要求在学生信息表中可以查找到该学号。于此同时，如果您要删除学生信息表中某个学号字段，则必须保证成绩表中没有引用该学号字段，否则无法删除该记录。这就是数据的一致性和完整性。

附上图解：

使用说明

创建外键

例如：创建一个产品表：

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
);

现在就不可能创建包含不存在于产品表中的product_no值（非空）的订单。

现在我们可以首先在产品表中插入三条记录，如下图所示：

INSERT INTO products 
VALUES 
(1, 'iphone', 5000),
(2, '小米手机', 2000),
(3, '华为手机', 3500);

假设其中两款手机有三条交易售卖情况，如下图所示：

INSERT INTO orders
VALUES 
(1, 1, 15),
(2, 1, 12),
(3, 3, 10);

创建一条包含不存在于产品表中的product_no值（非空）的订单：

INSERT INTO orders
VALUES 
(4, 5, 12),
(5, 3, 10);

提示错误如下：

insert or update on table "orders" violates foreign key constraint "orders_product_no_fkey"

删除一个被引用的产品，如下：

delete from products where product_no = 1;

提示错误如下：

update or delete on table "products" violates foreign key constraint "orders_product_no_fkey" on table "orders"

先删除产品相关订单再删除产品，如下：

delete from orders where product_no = 1; 
delete from products where product_no = 1;

则返回结果提示删除成功。

主键被作为引用列

我们说在这种情况下，订单表是引用表而产品表是 被引用 表。相应地，也有引用和被引用列的说法。

我们也可以把上述命令简写为：

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

因为如果缺少列的列表，则被引用表的主键将被用作被引用列。

一个外键也可以约束和引用一组列。照例，它需要被写成表约束的形式。下面是一个例子：

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

当然，被约束列的数量和类型应该匹配被引用列的数量和类型。

多个外键约束

一个表可以有超过一个的外键约束。这被用于实现表之间的多对多关系。例如我们有关于产品和订单的表，但我们现在希望一个订单能包含多种产品（这在上面的结构中是不允许的）。我们可以使用这种表结构：

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)
);

注意在最后一个表中主键和外键之间有重叠。

通过修改表结构来设置外键

给订单表表加外键，product_no列为产品表的主键，product_no在订单表中作为外键。操作步骤如下：

例如：创建一个产品表：

CREATE TABLE products (
    product_no integer PRIMARY KEY,
    name text,