具有外键约束的关系数据库如何摄取可能顺序错误的数据？

2024-05-18 • 问答

数据库正在从流中提取数据，并且满足外键约束所需的所有行都可能迟到或永远不会到达。

这很可能可以通过使用另一个没有外键约束的数据存储来实现，然后在所有需要的数据都可用时，将其读入具有fk约束的数据库中。但是，这增加了复杂性，我想避免这种情况。

我们正在研究一种创建“占位符”行以将外键指向的解决方案。当输入真实数据时，占位符将替换为真实值。同样，这增加了复杂性，但这是迄今为止我们发现的最佳解决方案。

人们通常如何解决这个问题？

编辑：一些示例数据可能有助于解释问题：

假设我们有以下表格：

CREATE TABLE order (
  id INTEGER NOT NULL,order_number,PRIMARY KEY (id),UNIQUE (order_number)
);

CREATE TABLE line_item (
  id INTEGER NOT NULL,order_number INTEGER REFERENCES order(order_number),PRIMARY KEY (id)
);

如果我先输入订单，那不是问题！但是，假设我尝试：

INSERT INTO line_item (order_number) values (123)插入订单123之前。当然，这将使fk约束失效。但这可能是我获取数据的顺序，因为它是从从多个来源收集此数据的流中读取的。

另外，要解决@philpxy的问题，我在此方面并没有发现太多。提到的一件事是deferred constraints。这是一种在事务结束时等待执行fk约束的机制。我认为在这种情况下是不可能的，因为每当接收到数据时，这些insert语句都会在随机时间运行。

您遇到了业务工作流程问题，因为单个订单的订单项是在订单本身进入之前进入的。一种解决方法（可能不理想）是创建一个插入前触发器，该触发器针对每个传入的插入内容进行检查line_item表中，该顺序是否已经存在于order表中。如果没有，那么它将首先插入订单记录，然后再尝试在line_item上插入。

CREATE OR REPLACE FUNCTION "public"."fn_insert_order" () RETURNS trigger AS $$
BEGIN
    INSERT INTO "order" (order_number)
    SELECT NEW.order_number
    WHERE NOT EXISTS (SELECT 1 FROM "order" WHERE order_number = NEW.order_number);
    RETURN NEW;
END
$$
LANGUAGE 'plpgsql'

# trigger 
CREATE TRIGGER "trigger_insert_order"
BEFORE INSERT ON line_item FOR EACH ROW
EXECUTE PROCEDURE fn_insert_order()

注意：我假设id表的order列实际上是自动递增的，在这种情况下，Postgres在如上所述插入时会自动为其分配一个值。很有可能，这就是您想要的，因为有两个都需要手动分配的id列没有多大意义。

您可以通过在BEFORE INSERT上使用line_item触发器来实现。在该触发器中，您查询order是否存在匹配项，如果不存在，则插入一个空行。

这将使INSERT成功，但会牺牲一些性能。

要在order中插入行，请使用

INSERT INTO order ...
ON CONFLICT ON (order_number) DO UPDATE SET
   id = EXCLUDED.id;

更新主键是有问题的，并且可能导致冲突。解决该问题的一种方法是，如果对人工生成的订单使用负的id（假设真实的id是正的）。如果您对该主键有任何引用，则必须使用ON UPDATE CASCADE定义约束。

具有外键约束的关系数据库如何摄取可能顺序错误的数据？

duanchongxixi 回答：具有外键约束的关系数据库如何摄取可能顺序错误的数据？

大家都在问