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

问题描述

$





$ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $'这里没有问题
街头字符变化（100）
zip字符变化
城市字符变化
public boolean $ b $ 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地址OWNER TO postgres;

表利益相关者（父）
CREATE TABLE利益相关者
（
pk_stakeholder整数DEFAULT nextval（'common_stakeholder_seq'）NOT NULL，
fk_stakeholder_type integer NOT NULL， - 此表也存在，这里没有问题
名称字符变化（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利益相关者OWNER TO postgres;

- 个人（利益相关者的孩子）
CREATE TABLE个人
（
名字变化（50），
fk_title整数， - this表格也存在，这里没问题
email1字符变化（100），
email2字符变化（100），
phone1字符变化（50），
phone2字符变化（50） ，
CONSTRAINT individual_primarykey PRIMARY KEY（pk_stakeholder），
CONSTRAINT title_foreignkey FOREIGN KEY（fk_title）
参考标题（pk_title）MATCH SIMPLE
ON UPDATE CASCADE ON DELETE CASCADE
） （利益相关者）
WITH（
OIDS = FALSE
）;
ALTER TABLE个人所有者到postgres;

- 利益相关者和地址之间的链接
CREATE TABLE l_stakeholder_address
（
pk_l_stakeholder_address serial NOT NULL，
fk_stakeholder integer NOT NULL REFERENCES利益相关者，
fk_address integer NOT NULL参考地址
CONSTRAINT l_stakeholder_address_primarykey PRIMARY KEY（pk_l_stakeholder_address），
CONSTRAINT l_stakeholder_address_fk_stakeholder FOREIGN KEY（fk_stakeholder）
REFERENCES利益相关者（pk_stakeholder）MATCH SIMPLE
ON UPDATE CASCADE ON DELETE NO ACTION，
CONSTRAINT l_stakeholder_address_fk_address FOREIGN KEY（fk_address）
REFERENCES地址（pk_address）MATCH SIMPLE
ON UPDATE CASCADE ON DELETE NO ACTION
）
WITH（
OIDS = FALSE
）;
ALTER TABLE l_stakeholder_address OWNER TO postgres;

到目前为止，没有问题。然后，我尝试添加一些值：

pre code> 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地址（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）不存在于表利益相关者。
前两个插入是好的，我可以看到他们在数据库中：

$ $ $ p> 利益相关者：
pk_stakeholder | ...
----------------------
1 | ...

地址：
pk_address | ...
--------------------
1 | ...


我做错了什么？我必须承认，我对PostgreSQL相当陌生（使用8.4），但我甚至不确定这是否是PG的问题，也许我只是缺乏一些基本的数据库设计理解...

无论哪种方式，现在我尝试了几乎所有我能想到的，我也尝试使FK延迟，如 PostgreSQL的：交易和外键问题，但不知何故，也不工作。您可以使用附加的表格来使用附加表格（code> individual_pks（individual_pk integer primary key））来保护它，并且所有的父键和子键都将使用触发器（非常简单 - 在插入时插入到 individual_pks ，删除时删除它，更新时更新它，如果它改变 individual_pk ）。 / p>

然后，您将外键指向此附加表而不是chi LD。或者忘记继承，并以旧的方式去做 - 只需要一个带有一些可为空的列的表。

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);

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          | ...

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.

解决方案

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屋！