From: David Weilers <david@lionhead.nl>
To: pgsql-sql <pgsql-sql@postgresql.org>
Subject: Foreign key constraint referencing a parent table
Date: Tue, 4 May 2010 15:49:45 +0200
Message-ID: <H00000670069e101.1272980985.lionhead2.lionhead.nl@MHS> (raw)
Hi,
I have a problem when inserting rows into my table tblfactuurpost that
references a table tblfactuur which is a parent to tblclientfactuur.
The actual row is inserted into tblfactuur.
The problem is that i cannot insert the row into tblfactuurpost (which
references the parent table tblfactuur) because PSQL complains the
referencing ID (2) is missing while it is actually *not* missing at all.
Thanks to anyone who can shed some light ...
I have the following table set-up:
CREATE TABLE tblfactuur (
id serial PRIMARY KEY,
number int NOT NULL,
year int NOT NULL CHECK (year>=1900 AND year<=2999),
vanaf date NOT NULL,
tot date NOT NULL CHECK (tot > vanaf),
total int -- totaalbedrag in centen
);
CREATE TABLE tblclientfactuur (
client int NOT NULL REFERENCES tblclient (id) ON DELETE RESTRICT
, PRIMARY KEY (id)
) INHERITS (tblfactuur);
CREATE TABLE tblfactuurpost (
id serial PRIMARY KEY,
factuur int NOT NULL REFERENCES tblfactuur (id) ON DELETE CASCADE
DEFERRABLE,
omschrijving varchar(128),
btw smallint DEFAULT 19, -- standaard 19% btw
aantal decimal(8,1) NOT NULL CHECK (aantal >= 0), -- aantal
perstuk int NOT NULL CHECK (perstuk >= 0), -- bedrag per stuk in
centen
amount int NOT NULL CHECK (amount >= 0), -- totaal bedrag in centen
position smallint CHECK (position >= 0)
);
This is the data in tblfactuur:
(db)=> select * from tblfactuur;
id | number | year | vanaf | tot | total
----+--------+------+------------+------------+--------
2 | 1 | 2010 | 2010-03-01 | 2010-04-01 | 397800
(1 row)
I try to insert:
insert into tblfactuurpost (factuur, omschrijving, btw, aantal, perstuk,
amount, position)
select ?, ?, ?, ?, ?, ?, count(*) from tblfactuurpost where factuur
= ?;
parameters: (2, Gespecialiseerde 24 uur zorg ma. t/m vr., 19, 156, 1425,
397800, 2)
Regards, David
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: david@lionhead.nl
Subject: Re: Foreign key constraint referencing a parent table
In-Reply-To: <H00000670069e101.1272980985.lionhead2.lionhead.nl@MHS>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox