如何在表的子集上创建外键约束？（涉及IT技术）

Question

我有一个参考表，例如OrderType，它收集不同类型的订单：

CREATE TABLE IF NOT EXISTS OrderType (name VARCHAR);
ALTER TABLE OrderType ADD PRIMARY KEY (name);
INSERT INTO OrderType(name) VALUES('sale-order-type-1');
INSERT INTO OrderType(name) VALUES('sale-order-type-2');
INSERT INTO OrderType(name) VALUES('buy-order-type-1');
INSERT INTO OrderType(name) VALUES('buy-order-type-2');

我希望从另一个表（例如SaleInformation）创建指向该表（OrderType）的FK约束。然而，我想表达的是并非所有OrderType行都符合该FK的目的（它应该只与销售相关的订单类型相对应）。

我考虑创建OrderType表的视图，只包含正确类型的行（视图为SaleOrderType），并将FK约束添加到该视图中，但是PostgreSQL会阻止此操作，提示：

ERROR: referenced relation "SaleOrderType" is not a table 

看起来我无法创建一个外键约束到一个视图（为什么？）。我的选择只剩下创建一个冗余的表来保存销售相关的订单类型吗？另一个选择是允许外键指向原始表，但这样做并没有严格表达我的限制。

- Marcus Junius Brutus

---

要不要像这样拥有OrderType结构：id_order_type作为序列(pk)，order_type作为字符变量，type_order_type作为字符变量，并从SaleInformation引用id_order_type？ - Houari

请编辑您的问题，并粘贴OrderType的CREATE TABLE语句。 - Mike Sherrill 'Cat Recall'

Answer 1

我认为你的架构应该像这样：

我认为您的模式应该是这样的

create table order_nature (
    nature_id int primary key,
    description text
);
insert into order_nature (nature_id, description)
values (1, 'sale'), (2, 'buy')
;

create table order_type (
    type_id int primary key,
    description text
);
insert into order_type (type_id, description)
values (1, 'type 1'), (2, 'type 2')
;

create table order_nature_type (
    nature_id int references order_nature (nature_id),
    type_id int references order_type (type_id),
    primary key (nature_id, type_id)
);

insert into order_nature_type (nature_id, type_id)
values (1, 1), (1, 2), (2, 1), (2, 2)
;

create table sale_information (
    nature_id int default 1 check (nature_id = 1),
    type_id int,
    foreign key (nature_id, type_id) references order_nature_type (nature_id, type_id)
);

如果外键子句也接受表达式，则销售信息可以省略nature_id列。

create table sale_information (
    type_id int,
    foreign key (1, type_id) references order_nature_type (nature_id, type_id)
);

注意外键中的1。

- Clodoaldo Neto

---

如果从“sale_information”表中省略nature_id列，如何使用两个列创建FK？ 对我来说，像这样似乎更正确：foreign key (type_id) references order_nature_type (1, type_id)或foreign key (type_id) references order_nature_type (type_id) where [order_nature_type].nature_id = 1只是我不确定它们是否有效。 如何实现这样的功能？ - orad

一种FK + check约束的变体：如果order_nature和order_type不是非常大的表，那么order_nature_type可以有一个单独的ID列，其值将为nature_id * some constant + type_id。检查约束可以进行除法或位运算AND以从组合ID中检索nature_id。是的，它有点丑陋。但我面临着类似的问题，这是迄今为止我想出的最不丑陋的东西。而另一种选择（越来越多的小表）是一个令人讨厌的问题，逐渐变成了噩梦。 - unbob

这在 MS SQL Server 中同样适用，只需进行一些小的语法更改即可。 - Reversed Engineer

Answer 2

您可以使用外键（FK）来确保参照完整性，并使用单独的CHECK约束来限制订单类型。如果您的OrderType值确实是这样结构化的，那么像这样的简单CHECK就足够了：

check (c ~ '^sale-order-type-')

在SaleInformation中，c是订单类型列。

如果实际上类型不是这样结构化的，那么你可以向OrderType添加某种类型标志（比如一个布尔值is_sales列），编写一个函数来使用该标志以确定一个订单类型是否为销售订单：

create or replace function is_sales_order_type(text ot) returns boolean as $$
    select exists (select 1 from OrderType where name = ot and is_sales);
$$ language sql

然后在您的检查中使用它：

check(is_sales_order_type(c))

当然，您不必使用布尔型的is_sales标志，您可以有更多的结构，is_sales仅用于说明目的。

- mu is too short