主页 / dba / 问题 / 132776
Accepted
Nishant
Asked: 2016-03-21 00:53:03 +0800 CST

具有附加约束的外键？

  • 772

有一个名为Item(id, name, cost)and的表Orders(id, bill_id, item_id, units)，用于跟踪下达的订单，其中相同的 bill_id表示它属于单个订单。

如果需要将项目作为item_id添加到Order表中，如何在 DB 中施加额外的约束，表明Item应该是“可用的”（在那个时间点）？项目手动确定为“可用”，在此场景中无法从数据库中的其他字段派生。

一种模式设计（我更喜欢）是添加一个具有“可用”和“不可用”字段的类型列。但是如何检查外键约束item_id不仅应该是Item表中的主键，它的类型也应该是“可用”？

这个Stack Overflow使用检查约束的答案似乎很接近，但这是唯一的方法吗？我觉得这对于 RDBMS 来说是一件微不足道的事情，还是这不是一个规范化的数据？

另一种模式设计（我不喜欢）是有一个名为“菜单”的表，它只能有可用的项目。问题是这张桌子在本质上会变得非常动态，并且它会根据物品的可用性不断变化。而且，我只是根据其状态从 Items 中创建一个子表，这似乎不是一个好主意。

以编程方式很容易做到这一点；但是，我如何在 RDBMS 中实现这一点？我喜欢数据库足够智能来处理这个问题的想法。

database-design postgresql

1 个回答

  1. Best Answer

    原来的答案是错误的！有关更正的版本，请参见编辑 1。

    原始答案

    一个了不起的解决方案需要视图或继承表中的列的外键，但不幸的是 PostgreSQL（我想那是你的 RDBMS，因为标签）没有这个（还）。

    我认为对组织数据的方式进行简单的更改就足够了：创建一个类似ItemsAvailableQuantity的表，将Item与其可用性连接起来，这将是订单中的引用。当一个项目不再可用时，DELETE它来自它。

    CREATE TABLE Item (
    id   serial  NOT NULL
  , name text    NOT NULL
  , cost numeric

  , PRIMARY KEY (id)
  , CONSTRAINT positive_cost
        CHECK (cost > 0)
    );

CREATE TABLE ItemAvailableQuantity (
    id       serial  NOT NULL
  , item_id  integer NOT NULL
  , quantity integer NOT NULL

  , PRIMARY KEY (id)
  , FOREIGN KEY (item_id)
        REFERENCES Item (id)
        ON UPDATE CASCADE
        ON DELETE CASCADE
  , CONSTRAINT postive_quantity -- This constraint is the same as
        CHECK (quantity > 0)    -- checking something like `available = TRUE`.
    );

CREATE TABLE ItemOrder (       -- Changed the name from `Order` because
    id      serial  NOT NULL   -- PostgreSQL refuses that name, somehow
  , bill_id integer NOT NULL
  , item_id integer NOT NULL
  , units   integer NOT NULL

  , PRIMARY KEY (id)
  , FOREIGN KEY (item_id)
        REFERENCES ItemAvailableQuantity (id)
        ON UPDATE CASCADE
        ON DELETE CASCADE
-- Uncomment when `Bill` table is ready
--  , FOREIGN KEY (bill_id)
--        REFERENCES Bill (id)
--        ON UPDATE CASCADE
--        ON DELETE CASCADE
  , CONSTRAINT positive_units
        CHECK (units > 0)
    );

    注意！当您的软件减少单位并达到 0 时，约束positive_units可能会导致问题。如果需要，请使其类似于CHECK >= 0，或者添加一个触发器，当每个or上的单位达到 0（或更少）DELETE时自动 -s 行。这将保留表ItemAvailableQuantity以仅包含实际可用的项目，这是我们希望从表ItemOrder中引用的内容。INSERTUPDATE

    这应该可以解决您的问题。这不是您问题的确切答案。这将涉及触发器或CHECK调用您提供的链接中的函数。

    然后，要轻松查看项目的数量，只需创建一个连接ItemAvailableQuantity和Item的视图。如果你真的想要那么，用触发器使它成为INSERT-able （见黄框警告）。

    编辑 1

    实际上Order（又名ItemOrder）应该引用Item而不是ItemAvailableQuantity以避免在Item当前不可用时出现任何问题，如评论中所述。

    这建议我们应该删除整个表ItemAvailableQuantity并且只在Item上添加一个列available_quantity。

    CREATE TABLE Item (
    id                 serial NOT NULL
  , name               text   NOT NULL
  , cost               numeric
  , available_quantity integer NOT NULL

  , PRIMARY KEY (id)
  , CONSTRAINT positive_cost
        CHECK (cost > 0)
  , CONSTRAINT non_negative_quantity
        CHECK (quantity >= 0)
    );

    然后，为了确定只将可用项目插入到订单中，我们可以运行

    INSERT INTO ItemOrder (bill_id, item_id, units) VALUES
    (SELECT id FROM Bill WHERE condition = something -- customize at will
    , SELECT id FROM Item WHERE available_quantity >= wanted_quantity
        AND other_condition = something
    , wanted_quantity)
    ;

    wherewanted_quantity是您的软件传递给查询的参数。

    仍然，解决了问题，但不是问题的直接答案。

    • 3

相关问题