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

From: Manuel Cano Muñoz <manuel(at)adai-it(dot)com>
To: Lista de PostgreSQL <pgsql-general(at)postgresql(dot)org>
Subject: Re: Referential integrity doesn't work? (Thanks a lot)
Date: 2002-08-02 01:33:19
Message-ID: 1028252000.6257.67.camel@linux
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Browse pgsql-general by date

  From Date Subject
Next Message Ben Liblit 2002-08-02 01:52:41 huge performance penalty from constraint triggers
Previous Message Christopher Kings-Lynne 2002-08-02 01:13:06 Re: getpid() function