Databases
StackOverflow2013
Postsdbo
POPostgreSQL foreign key not existing, issue of inheritance?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POPostgreSQL foreign key not existing, issue of inheritance?
    primarykey
    data
    text
    <p>I am struggling with foreign keys in my DB, possibly it has something to do with inheritance?<br> So here's the basic setup:</p> <pre><code>-- 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; </code></pre> <p>So far, no problem. Then I tried to add some values:</p> <pre><code>INSERT INTO individual (pk_stakeholder, fk_stakeholder_type, name, firstname, fk_title, email1, email2, phone1, phone2) VALUES (1, 8, 'Lastname', 'Firstname', 1, 'me@you.com', '', '', ''); 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); </code></pre> <p>And finally I end up having an error (SQL state 23503) saying that the key (fk_stakeholder)=(1) is not existing in table "stakeholder".<br> The first 2 inserts are fine, I can see them in the databases:</p> <pre><code>stakeholder: pk_stakeholder | ... ---------------------- 1 | ... address: pk_address | ... -------------------- 1 | ... </code></pre> <p>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 ...<br> Either way, by now I tried pretty much everything I could think of, I also tried to make the FK deferrable as in <a href="https://stackoverflow.com/questions/3730694/postgresql-transaction-and-foreign-key-problem">PostgreSQL : Transaction and foreign key problem</a> but somehow that doesn't work either.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USLukas
    plurals
    1. PL
      singulars
      1. LTLinked
    1. PL
      singulars
      1. LTLinked
    2. PL
      singulars
      1. LTLinked
    3. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POPostgreSQL foreign key not existing, issue of inheritance?
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POPostgreSQL foreign key not existing, issue of inheritance?
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POPostgreSQL foreign key not existing, issue of inheritance?
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload