postgresql - 是否可以将 View 中的列引用为外键(PostgreSQL 9.4)？

Question

我知道在旧版本中这是不可能的，它与 9.4 版本一样吗？

我正在尝试做这样的事情:

CREATE VIEW products AS 
    SELECT d1.id AS id, d1.price AS pr FROM dup.freshProducts AS d1
    UNION
    SELECT d2.id AS id, d2.price AS pr FROM dup.cannedProducts AS d2;

CREATE TABLE orderLines
( 
    line_id integer PRIMARY KEY, 
    product_no integer REFERENCES productView.id
);

我正在尝试实现一种继承关系，其中 freshProducts 和 cannedProducts 都继承自 products。我使用两个不同的表来实现它，并创建了一个 View products，它只具有 freshProducts 和 cannedProducts 之间的公共(public)属性。此外，orderLines 中的每一行都与一个product 相关，freshProduct 或cannedProduct。请参阅图片进行说明。

如果无法引用 View ，您认为哪种解决方案最好？我想到了物化 View 或使用触发器实现限制。您能否推荐此类触发器的任何良好示例作为基础？

非常感谢!

Answer 1

引用(物化) View 是行不通的，触发器可能如下所示:

CREATE OR REPLACE FUNCTION reject_not_existing_id()
    RETURNS "trigger" AS
    $BODY$
        BEGIN
            IF NEW.product_no NOT IN (SELECT id FROM dup.freshProducts UNION SELECT id FROM dup.cannedProducts) THEN
                RAISE EXCEPTION 'The product id % does not exist', NEW.product_no;
            END IF;
            RETURN NEW;
        END;
    $BODY$
        LANGUAGE 'plpgsql' VOLATILE;

CREATE TRIGGER tr_before_insert_or_update
    BEFORE INSERT OR UPDATE OF product_no
    ON orderLines
    FOR EACH ROW
    EXECUTE PROCEDURE reject_not_existing_id();

(另见 http://www.tek-tips.com/viewthread.cfm?qid=1116256)

物化 View 可能看起来是个好方法，但失败的原因有两个:就像 View 一样，您根本无法引用它，因为它不是表(继续尝试)。假设你可以，在 freshProducts 和 cannedProducts 中仍然存在防止两个相同 ID 的问题。是的，您可以在物化 View 上定义一个 UNIQUE INDEX，但是如何确保相同的 id 不首先用于新鲜和 jar 头？如果在 orderLines 中使用触发器，这是您仍然需要解决的问题。

这让我建议重新考虑您的模型。 'Fresh' 和 'canned' 也可能是单个表 products 的属性值，因此所有的麻烦都是多余的。如果新鲜产品和 jar 装产品在属性(数量)上存在显着差异(想不出创建两个不同表的任何其他原因)，则在其他两个表中引用产品 ID。喜欢

CREATE TABLE products
(
    id ... PRIMARY KEY
    , fresh_or_canned ...
    , price ...
    , another_common_attribute_1 ...
    , ...
    , another_common_attribute_n ...
);

CREATE TABLE canned_specific_data
(
    canned_id ... REFERENCES products (id)
    , type_of_can ...
    , ...
    , another_attribute_that_does_not_apply_to_fresh ...
);

CREATE TABLE fresh_specific_data
(
    fresh_id ... REFERENCES products (id)
    , date_of_harvest ...
    , ...
    , another_attribute_that_does_not_apply_to_canned ...
);