Re: Referential integrity doesn't work?

On 1 Aug 2002, Manuel Cano Muñoz wrote:

> El jue, 01-08-2002 a las 20:42, Stephan Szabo escribió:
> >
> > On 1 Aug 2002, Manuel Cano [ISO-8859-1] Muñoz wrote:
> >
> >
> > > liman@linux:~/proyectos/cange> psql prueba
> > > Welcome to psql, the PostgreSQL interactive terminal.
> > >
> > > Type:  \copyright for distribution terms
> > >        \h for help with SQL commands
> > >        \? for help on internal slash commands
> > >        \g or terminate with semicolon to execute query
> > >        \q to quit
> > >
> > > prueba=# select * from conceptos;
> > >  id | id_tabla1 |         descripcion         | borrado
> > > ----+-----------+-----------------------------+---------
> > >   0 |           | Este es el primer registro  | f
> > >   1 |           | Este es el segundo registro | f
> > >   2 |           | Este es el tercer registro  | f
> > > (3 rows)
> >
> > It looks to me that it's either treating id_tabla1 as
> > NULL (which passes the constraint) or 0 (which passes the
> > constraint).  What version are you using?
> >
> I don't understand you. Do you mean that if the id_tabla1 is
> NULL or 0 the REFERENCE (not my trigger) constraint is not
> enforced? That means that if I try to insert a record without
> a value it will pass, and I think the referential integrity
> is there to forbid just that.
>
> Here is the insert statement that really insert a row even if
> it provides no valid foreign key:
>
> INSERT INTO conceptos (id, id_tabla1, descripcion, borrado) VALUES ('0',
> '', 'Este es el primer registro', 'f');
> ^ Empty foreign key.
>
> There is a REFERENCE keyword that should prohibit this happening:
>
> CREATE TABLE conceptos ( ...
> id_tabla1 int CONSTRAINT conceptos_ref_id REFERENCES tabla1(id) ON
> UPDATE cascade ON DELETE restrict DEFERRABLE INITIALLY DEFERRED, ...
>
> This reference should force me to provide a valid id_table1 value,
> but it doesn't.
>
> Am I misunderstanding something?
>

I've tried this on my 7.2.1 system and I get '0' returned in the column you see
as empty, i.e. NULL. The full version information is:

testindex=# select version();
                            version
---------------------------------------------------------------
 PostgreSQL 7.2.1 on i686-pc-linux-gnu, compiled by GCC 2.95.2
(1 row)


Manuel, what version are you using?


My results seem to give wrong results also, even when I create the table having
removed the INITIALLY DEFERRED words from the column definition. So I would say
that Manuel was somehow seeing the empty string, '', translated into NULL but
then why am I seeing it changed into 0? Oh wait, my 0 is going to be the result
of atoi() on an empty string, which does pass the RI test. So I suppose the
question is which version is Manuel using and why is it giving NULL instead of
zero?

I've just tested this on my 7.3dev, which is a few weeks old now, but I get the
0 so I presume Manuel is using some older version.

One could say that this atoi() conversion of an empty string is an accident
waiting to happen when it comes to data integrity. Other than detecting it and
giving NULL, which I don't know how to even start working out how to do, then I
think the best that can be done is to have some mention in the documentation
somewhere that using 0 for a valid target item of a foriegn key should be
avoided.

Sorry for the length of this message, I couldn't really see anything in the
quoted section that was relevent and could therefore be cut and as usual I've
rambled on in what I've added.


--
Nigel J. Andrews
Director

---
Logictree Systems Limited
Computer Consultants