Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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


pgsql-novice by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group