Re: Referential integrity doesn't work?

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?




Manuel Cano