Re: Referential integrity doesn't work? (Thanks a lot)

First thanks a lot for your help.

Now I'm not having 0 as a valid value in the ID fields (PRIMARY KEY,
FOREIGN, etc), that was an error of mine. The problem was the insert
whas inserting NULL values, and the trigger doesn't catch those. Now
I've changed the field to NOT NULL and all works fine.

Anyway I found a bit clumsy that referential integrity can't catch NULL
values, because forget to put a field NOT NULL is easy, and that almost
ruins referential integrity.

Say you have one table: create one (id serial primary key, char(5));
And another: create table two (id serial primary key, id_one int
reference one, description varchar(80));

This allows you to create "bogus" inserts like this:

insert into two (description) values ('Referential integrity
compromised');

Because of the id_one being NULL, you can insert all non-sense data into
your "integrity safe" tables.

Well, we all know a good database admin/designer/... should write ID
fields NOT NULL, but... I would like the DBMS catching those for me.

Thanks a lot to everybody.

Manuel Cano

P.S.: i'm using PostgreSQL 7.2