Re: integrity and inherit

Bo Lorentsen wrote:
  >I hope someone will be so kind to take the time to explain to me what I
  >have been doing wrong in this 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
  >);
...
  >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

Referential integrity doesn't support references to inheritance hierarchies.
You will have to redesign your database.

For example, have a trigger on each table in the hierarchy to update a
table of keys and use RI on that; it would also help you to maintain
primary keys -- you probably think that table derived inherits the
primary key constraint from base, but it doesn't.  That is another problem
with the current implementation of inheritance. :-(

--
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47  6B 7E 39 CC 56 E4 C1 47
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
                 ========================================
     "For God hath not appointed us to wrath, but to obtain
      salvation by our Lord Jesus Christ, Who died for us,
      that, whether we wake or sleep, we should live
      together with him."
                         I Thessalonians 5:9,10