设计具有混合节点类型的树层次结构(继承无济于事！

Question

我有一个关于在postgresql（9.1）中实现继承的问题。

目的是建立一个地理层次结构模型，其中国家，州和大陆可以混合在一起以创建"区域"。然后这些区域也可以与国家等混合在一起，以创建一个真正令人敬畏的区域层次结构

所以在我的逻辑模型中，一切都是一种"地方"。可以通过使用两个"位置"按边指定来构造区域树。设计如下，易于在Java层中管理。

    create table place_t (
            place_id serial primary key,
            place_type varchar(10)
    );
    create table country_t (
            short_name varchar(30) unique, 
            name varchar(255) null
    ) inherits(place_t);
    create table region_t( 
            short_name varchar(30),
            hierarchy_id integer, -- references hierarchy_t(hierarchy_id)
            unique(short_name) -- (short_name,hierarchy_id)
    ) inherits(place_t);
    create table region_hier_t( 
            parent integer references place_t(place_id), -- would prefer FK region_t(place_id)
            child integer references place_t(place_id),
            primary key(parent,child)
    );
    insert into region_t values(DEFAULT, 'region', 'NA', 'north american ops');
    insert into region_t values(DEFAULT, 'region', 'EMEA', 'europe and middle east');
    insert into country_t values(DEFAULT, 'country', 'US', 'USD', 'united states');
    insert into country_t values(DEFAULT, 'country', 'CN', 'CND', 'canada');

目前为止，一切都好。但以下方法失败：

    insert  into region_hier_t
        select p.place_id, c.place_id
        from region_t as p, country_t as c
        where p.short_name = 'NA' and c.short_name = 'US';

原因是前 4 个插入没有在"place_t"中创建任何行。嗡嗡！Postgres文档实际上提到了这一点。

问题是 - 有解决方法吗？通过region_t和country_t上的插入触发器来实现我自己的"继承"是我唯一能想到的。

第二个问题是 - 对于这种混合节点树结构，是否有更好的设计？

出于某些原因，我不想过分依赖postgres-contrib功能。也许这很愚蠢，请随时插话，但要轻轻地（并且只有在回答另一个问题之后）！

谢谢

Answer 1

表中

父列和子列region_hier_t引用是错误的，因为如果您的引用调用另一个表(child integer references place_t(place_id))，则无法插入country_t中的键;您可以删除它们或添加新的。因此，让我们采用第二个选项，并为引用的表添加与region_t和country_t的给定键匹配的唯一约束：

ALTER TABLE region_t
  ADD CONSTRAINT pk_region_t PRIMARY KEY(place_id );
ALTER TABLE country_t
  ADD CONSTRAINT pk_country_t PRIMARY KEY(place_id );

region_hier_t的正确 CREATE 语句是：

        create table region_hier_t( 
            parent integer references region_t(place_id),
            child integer references country_t(place_id),
            primary key(parent,child)
    );

最后，您可以运行INSERT.

因此，如您所见，有许多改进需要您做。也许你应该重新考虑你的设计。看看这个答案：如何以规范化的方式存储邮政地址和政治分歧？它比您的解决方案简单得多，并且更易于维护。

但是，如果您想保留解决方案，请不要忘记在子表上设置主键（如上所示）。只有检查约束和非空约束由其子项继承，而您尚未这样做。

我看到您的其他插入无法正常工作：

insert into region_t values(DEFAULT, 'region', 'NA', 'north american ops');

ERROR:  invalid input syntax for integer: "north american ops"
LINE 1: ...ert into region_t values(DEFAULT, 'region', 'NA', 'north ame...

所以列分配也存在问题。

Answer 2

因此，PostgreSQL中的继承与典型OOP语言中使用的继承有些不同。特别是，"超类"表不会自动填充。如果我必须使用自己的触发器来执行此操作，则继承结构没有用例。

所以我放弃了Postgresql继承，创建了自己的"place_t"表。以及"country_t"、"state_t"、"county_t"和"region_t"子表，链接到父"place_t"到"place_id"。

在这些子表上，我创建了一个插入/更新行级别触发器，以确保"place_id"引用"place_t"中的有效行，并且以后不会更改引用。IOW，子表中的"place_id"应表现为一次写入多次读取多次。

现在，我可以插入世界地理。此外，定义一个新的"区域"。我创建了一个"region_composition_t"来记录区域层次结构的边缘，父级是对"region_t"的引用，子级是对"place_t"的引用。

目前为止，一切都好。现在的挑战是如何抑制任何更新/删除级联效应。

Answer 3

解决方法

是删除外键以place_t并改为执行以下操作：

CREATE FUNCTION place_t_exists(id int)
RETURNS bool LANGUAGE SQL AS 
$$
   SELECT count(*) = 1 FROM place_t;
$$;
CREATE FUNCTION fkey_place_t() RETURNS TRIGGER
LANGUAGE PLPGSQL AS $$
BEGIN;
  IF place_t_exists(TG_ARGV[1]) THEN RETURN NEW
  ELSE RAISE EXCEPTION 'place_t does not exist';
  END IF;
END;
$$;

当层次结构节点存在时，您还需要对子表进行约束：

CREATE FUNCTION hierarchy_exists(id int) RETURNS BOOL LANGUAGE SQL AS
$$
SELECT COUNT(*) > 0 FROM region_heir_t WHERE parent = $1 or child = $1;
$$;
CREATE OR REPLACE FUNCTION fkey_hierarchy_trigger() RETURNS trigger LANGUAGE PLPGSQL AS
$$
BEGIN
   IF hierarchy_exists(old.place_id) THEN RAISE EXCEPTION 'Hierarchy node still exists';
   ELSE RETURN OLD;
END;
$$;

然后，您可以创建触发器：

CREATE CONSTRAINT TRIGGER fkey_place_parent AFTER INSERT OR UPDATE TO region_hier_t
FOR EACH ROW EXECUTE PROCEDURE fkey_place_t(new.parent);
CREATE CONSTRAINT TRIGGER fkey_place_child AFTER INSERT OR UPDATE TO region_hier_t
FOR EACH ROW EXECUTE PROCEDURE fkey_place_t(new.child);

然后对于每个place_t子表：

CREATE CONSTRAINT TRIGGER fkey_hier_t TO [child_table]
FOR EACH ROW EXECUTE PROCEDURE fkey_hierarchy_trigger();

这个解决方案可能不值得，但如果需要，值得知道如何去做。