Re: Referential integrity doesn't work?

On Thu, 2002-08-01 at 16:59, Manuel Cano Muñoz wrote:
> > >
> > > 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 tried to duplicate your findings and two things of note occured.
First, on postgres 7.2 your function actualiza_cuenta() doesn't seem to
execute properly, which causes any inserts to fail by throwing an error.
This begs the question of what version your running. Second, when I
don't try and use your trigger, I find that postgres interprets your
insert

"INSERT INTO conceptos (id, id_tabla1, descripcion, borrado) VALUES
('0', '', 'Este es el primer registro', 'f');"

AS

"INSERT INTO conceptos (id, id_tabla1, descripcion, borrado) VALUES
('0', 0, 'Este es el primer registro', 'f');"

my assumption for this is that your trying to insert a blank '' into an
int field, which would not be valid, so postgres converts it to a 0 for
you. (perhaps becuase 0 = '' in some sense). Since you have an id of 0
in the tabla1 table, it allows the insert. If I change the '' to
something like 666, it fails. So AFAICT it is working as it should on
pg7.2.1, but you might be having trouble becuase your running an old
version, or there might be some syntax issues that might need to be
worked out in your script...

Robert Treat