sql - 跨表实现约束的最佳方式是什么？

Question

我经常发现自己遇到这样的情况(这是人为的但说明了问题):

CREATE TABLE customer (
  id   SERIAL PRIMARY KEY,
  type TEXT
  -- other columns...
);

CREATE TABLE product_order (
  id          SERIAL PRIMARY KEY,
  customer_id INTEGER REFERENCES customer (id),
  type        TEXT REFERENCES customer (type), -- not actually legitimate
  -- other columns...
  CHECK (type = 'business')
);

当然，product_order.type 上的外键约束不起作用，因为 customer.type 不是 UNIQUE 也不是主键(而且我不能在仅存在于另一个表中的列上使用 CHECK CONSTRAINT)。但是，我只想要 type = 'business' 客户的 product_order 条目。

我可以使 customer.id 和 customer.type 成为复合主键，但是任何其他表只想引用 customer.id 还必须不必要地引用 customer.type。

在这种情况下最好的方法是什么？

编辑:忘记了外键约束 product_order.customer_id!

Answer 1

如果您在 customer.type 上创建一个唯一的约束，您可以从 product_order 表中引用它:

CREATE TABLE customer (
  id   SERIAL PRIMARY KEY,
  type TEXT,
  -- other columns...
  constraint unique_cust_type unique (id, type) -- this makes the combination id/type "referencable"
);


CREATE TABLE product_order 
(
  id          SERIAL PRIMARY KEY,
  customer_id INTEGER,
  type        TEXT default 'business',
  CHECK (type = 'business'),
  foreign key (customer_id, type) references customer (id, type)
);