代码之家 › 专栏 › 技术社区 › Jordi

是否可以将视图中的列引用为外键(PostgreSQL 9.4)?

postgresql-9.4 postgresql

Jordi · 技术社区 · 9 年前

我知道在旧版本中这是不可能的,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 。我使用两个不同的表实现了它,并创建了一个视图 产品 仅具有以下共同属性 fresh产品 和 罐头产品 。此外 orderLines 与 product ,或者 freshProduct 或 cannedProduct 。请参见图片以进行说明。

What I'm trying to model.

如果无法引用视图,您认为哪种解决方案最好?我想到了使用实体化视图或使用触发器实现限制。你能推荐一个这样的触发器的好例子作为基础吗?

非常感谢!

2 回复 | 直到 9 年前

MyBrainHurts 9 年前

引用(具体化)视图不起作用,触发器可能如下所示:

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 )

具体化视图看起来可能是一种很好的方法,但失败的原因有两个:像视图一样,您根本无法引用它,因为它没有表(请继续尝试)。假设可以,那么在 freshProducts 和 cannedProducts 是的,你可以在一个具体化视图上定义一个UNIQUE INDEX,但是如何确保同一个id首先不在新鲜的和罐装的视图中使用? 如果在 orderLines .

这让我建议重新思考你的模式。”“Fresh”和“canned”也可以是单个表的属性值 products 因此,所有的麻烦都是多余的。如果新鲜产品和罐装产品在属性(数量)上存在显著差异(无法想到创建两个不同表的其他原因),则在其他两个表中引用产品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 ...
);

Robert Stoddard 7 年前

防止ID重复的简单方法是简单地使用与freshProducts和cannedProducts中ID的默认值相同的序列。

现在,问题来了,为什么你需要一个外键?通常,这是为了防止删除另一个表所依赖的数据,但是,您可以编写触发器来防止这种情况。此外,您可以将该值更新为键控表中不存在的值,但也可以为此编写触发器。

因此,基本上,您可以编写触发器来实现外键的所有所需功能,而无需实际使用外键,另外还有一个好处,即它们可以与这样的视图一起工作。