integrity and inherit

From: Bo Lorentsen <bl(at)netgroup(dot)dk>
To: "pgsql-novice(at)postgresql(dot)org" <pgsql-novice(at)postgresql(dot)org>
Subject: integrity and inherit
Date: 2001-08-23 10:20:03
Message-ID: 3B84D8D3.14F8EE7D@netgroup.dk
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

Hi ...

I hope someone will be so kind to take the time to explain to me what I
have been doing wrong in this example.

I have used PosgreSQL now for a while, and I really like to begin to use
some more of its integrity checks. The database I have been creating
uses the inherit functionality of PG, and this works out beautiful :-)

But I can't make the combination work ! To point out the problem i have
made this small example :

CREATE TABLE base (
id INTEGER PRIMARY KEY,
name TEXT
);

CREATE TABLE derived (
value INTEGER
) INHERITS( base );

CREATE TABLE test_ref (
ref_id INTEGER REFERENCES base,
name TEXT
);

This is the setup, and all I get is a few "NOTICE", about indirect check
and indexes, really nice. Time to use it :-)

INSERT INTO base (id, name) VALUES( 1, 'mother' );
INSERT INTO derived (id, name, value) VALUES( 2, 'child', 42 );

INSERT INTO test_ref (ref_id, name) VALUES( 1, 'mother' );

All this works, but this one dont :

INSERT INTO test_ref (ref_id, name) VALUES( 2, 'child' );

Here it says :

ERROR: <unnamed> referential integrity violation - key referenced from
test_ref not found in base

But a quick :

SELECT * FROM base;

shows :

id | name
----+--------
1 | mother
2 | child

So as fare as I can see, this isn't correct, the id value number 2 is in
"base" table.

Could someone please tell me what I have done wrong ?

/BL

Browse pgsql-novice by date

  From Date Subject
Next Message Albert Reiner 2001-08-23 12:12:32 Re: [NOVICE] protected ON DELETE CASCADE
Previous Message Jason Earl 2001-08-22 23:52:37 Re: Text Import