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

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

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

On 1 Aug 2002, Robert Treat wrote:

...
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...

I forgot to mention I tried the script having commented out the function and
trigger because I didn't have pl/pgsql loaded in the database I was using for
it.

However, the mention of the function failing raises a question for me. I don't
think I've seen this in the documentation, my apologies if it's there or a
blindingly obvious fact, so does the trigger get run if I do something like:

BEGIN;
INSERT INTO mytable VALUES (...);
CREATE TRIGGER mytrigger BEFORE INSERT OR UPDATE ON mytable blah, blah, blah...
COMMIT;

I would have said not but Robert's comment seems to be saying it is.

--
Nigel J. Andrews
Director

---
Logictree Systems Limited
Computer Consultants

On Thu, 2002-08-01 at 17:04, Nigel J. Andrews wrote:

On 1 Aug 2002, Robert Treat wrote:

...
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...

I forgot to mention I tried the script having commented out the function and
trigger because I didn't have pl/pgsql loaded in the database I was using for
it.

However, the mention of the function failing raises a question for me. I don't
think I've seen this in the documentation, my apologies if it's there or a
blindingly obvious fact, so does the trigger get run if I do something like:

BEGIN;
INSERT INTO mytable VALUES (...);
CREATE TRIGGER mytrigger BEFORE INSERT OR UPDATE ON mytable blah, blah, blah...
COMMIT;

I would have said not but Robert's comment seems to be saying it is.

I think not is correct. It only attempted to pull the trigger upon
subsequent inserts I tried (having already issued the commit).

Robert Treat