PostgreSQL 外键不存在，继承问题? [英] PostgreSQL foreign key not existing, issue of inheritance?

问题描述

我在数据库中遇到外键问题，可能与继承有关?
下面是基本设置:

I am struggling with foreign keys in my DB, possibly it has something to do with inheritance?
So here's the basic setup:

-- table address
CREATE TABLE address
(
  pk_address serial NOT NULL,
  fk_gadmid_0 integer NOT NULL, -- this table already exists, no problem here
  street character varying(100),
  zip character varying(10),
  city character varying(50),
  public boolean,
  CONSTRAINT address_primarykey PRIMARY KEY (pk_address),
  CONSTRAINT gadmid_0_primarykey FOREIGN KEY (fk_gadmid_0)
      REFERENCES adm0 (gadmid_0) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
);
ALTER TABLE address OWNER TO postgres;  

-- table stakeholder (parent)
CREATE TABLE stakeholder
(
    pk_stakeholder integer DEFAULT nextval('common_stakeholder_seq') NOT NULL,
    fk_stakeholder_type integer NOT NULL, -- this table also exists, no problem here
    name character varying(255) NOT NULL,
    CONSTRAINT stakeholder_primarykey PRIMARY KEY (pk_stakeholder),
    CONSTRAINT stakeholder_fk_stakeholder_type FOREIGN KEY (fk_stakeholder_type)
        REFERENCES stakeholder_type (pk_stakeholder_type) MATCH SIMPLE
        ON UPDATE CASCADE ON DELETE NO ACTION
)
WITH (
    OIDS=FALSE
);
ALTER TABLE stakeholder OWNER TO postgres;  

-- table individual (child of stakeholder)
CREATE TABLE individual
(
    firstname character varying(50),
    fk_title integer, -- this table also exists, no problem here
    email1 character varying (100),
    email2 character varying (100),
    phone1 character varying (50),
    phone2 character varying (50),
    CONSTRAINT individual_primarykey PRIMARY KEY (pk_stakeholder),
    CONSTRAINT title_foreignkey FOREIGN KEY (fk_title)
        REFERENCES title (pk_title) MATCH SIMPLE
        ON UPDATE CASCADE ON DELETE CASCADE
) INHERITS (stakeholder)
WITH (
    OIDS=FALSE
);
ALTER TABLE individual OWNER TO postgres;  

-- link between stakeholder and address
CREATE TABLE l_stakeholder_address
(
    pk_l_stakeholder_address serial NOT NULL,
    fk_stakeholder integer NOT NULL REFERENCES stakeholder,
    fk_address integer NOT NULL REFERENCES address,
    CONSTRAINT l_stakeholder_address_primarykey PRIMARY KEY (pk_l_stakeholder_address),
    CONSTRAINT l_stakeholder_address_fk_stakeholder FOREIGN KEY (fk_stakeholder)
        REFERENCES stakeholder (pk_stakeholder) MATCH SIMPLE
        ON UPDATE CASCADE ON DELETE NO ACTION,
    CONSTRAINT l_stakeholder_address_fk_address FOREIGN KEY (fk_address)
        REFERENCES address (pk_address) MATCH SIMPLE
        ON UPDATE CASCADE ON DELETE NO ACTION
)
WITH (
    OIDS=FALSE
);
ALTER TABLE l_stakeholder_address OWNER TO postgres;

到目前为止，没问题.然后我尝试添加一些值:

So far, no problem. Then I tried to add some values:

INSERT INTO individual (pk_stakeholder, fk_stakeholder_type, name, firstname, fk_title, email1, email2, phone1, phone2) 
  VALUES (1, 8, 'Lastname', 'Firstname', 1, '[e-mail]', '', '', '');
INSERT INTO address (pk_address, fk_gadmid_0, street, zip, city, public)  
  VALUES (1, 126, 'Address', '', 'City', FALSE);
INSERT INTO l_stakeholder_address (pk_l_stakeholder_address, fk_stakeholder, fk_address)  
  VALUES (DEFAULT, 1, 1);

最后我遇到了一个错误(SQL 状态 23503)，说表利益相关者"中不存在密钥 (fk_stakeholder)=(1).
前 2 个插入很好，我可以在数据库中看到它们:

And finally I end up having an error (SQL state 23503) saying that the key (fk_stakeholder)=(1) is not existing in table "stakeholder".
The first 2 inserts are fine, I can see them in the databases:

stakeholder:
pk_stakeholder | ...
----------------------
1              | ...

address:
pk_address | ...
--------------------
1          | ...

我做错了什么?我必须承认我对 PostgreSQL 相当陌生(使用 8.4)，但我什至不确定这是否是 PG 的问题，也许我只是缺乏一些基本的数据库设计理解......
无论哪种方式，到目前为止，我已经尝试了几乎所有我能想到的东西，我还尝试使 FK 可延迟，如 PostgreSQL : 事务和外键问题 但不知何故这也不起作用.

What am I doing wrong? I must admit that I am rather new to PostgreSQL (using 8.4) but I'm not even sure if that is an issue of PG at all, maybe I'm just lacking some basic database design understandings ...
Either way, by now I tried pretty much everything I could think of, I also tried to make the FK deferrable as in PostgreSQL : Transaction and foreign key problem but somehow that doesn't work either.

推荐答案

您可以使用附加表 individual_pks (individual_pk 整数主键) 来解决它，其中包含来自父子节点的所有主键，这将使用触发器进行维护(非常简单 - 在插入时插入到 individual_pks，在删除时从中删除，在更新时更新它，如果它更改了 individual_pk).

You can work around it using additional table individual_pks (individual_pk integer primary key) with all primary keys from both parent and child, which will be maintained using triggers (very simple — insert to individual_pks on insert, delete from it on delete, update it on update, if it changes individual_pk).

然后你将外键指向这个额外的表而不是一个子表.会有一些小的性能影响，但仅限于添加/删除行时.

Then you point foreign keys to this additional table instead of a child. There'll be some small performance hit, but only when adding/deleting rows.

或者忘记继承并使用旧方法 - 只需一个包含一些可为空列的表.

Or forget inheritance and do it the old way - simply one table with some nullable columns.

这篇关于PostgreSQL 外键不存在，继承问题?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！