Date: Tue, 4 May 2010 15:49:45 +0200
From: "David Weilers" <david@lionhead.nl>
To: pgsql-sql <pgsql-sql@postgresql.org>
Message-ID: <H00000670069e101.1272980985.lionhead2.lionhead.nl@MHS>
Subject: Foreign key constraint referencing a parent table
MIME-Version: 1.0
Content-Type: text/plain;
	charset="US-ASCII"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

Hi,

I have a problem when inserting rows into my table tblfactuurpost that=20
references a table tblfactuur which is a parent to tblclientfactuur. =20
The actual row is inserted into tblfactuur.

The problem is that i cannot insert the row into tblfactuurpost (which=20
references the parent table tblfactuur) because PSQL complains the=20
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>=3D1900 AND year<=3D2999),

	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=20
DEFERRABLE,

	omschrijving varchar(128),

	btw smallint DEFAULT 19, -- standaard 19% btw
	aantal decimal(8,1) NOT NULL CHECK (aantal >=3D 0), -- aantal
	perstuk int NOT NULL CHECK (perstuk >=3D 0), -- bedrag per stuk in=20
centen
	amount int NOT NULL CHECK (amount >=3D 0), -- totaal bedrag in centen

	position smallint CHECK (position >=3D 0)
);

This is the data in tblfactuur:

(db)=3D> 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:=20

insert into tblfactuurpost (factuur, omschrijving, btw, aantal, perstuk,=20

amount, position)
	select ?, ?, ?, ?, ?, ?, count(*) from tblfactuurpost where factuur=20

=3D ?;
parameters: (2, Gespecialiseerde 24 uur zorg ma. t/m vr., 19, 156, 1425,=20

397800, 2)

Regards, David