Thread: Referential integrity doesn't work?
Hello.
I'm having a problem. I've created this SQL statements in a script, and
I "cat" the script to psql (cat script.sql | psql <database>).
[script begin]
BEGIN;
CREATE SEQUENCE conceptos_id_seq START 3 INCREMENT 1 MAXVALUE 2147483647
MINVALUE 1;
CREATE SEQUENCE contador_id_seq START 1 INCREMENT 1 MAXVALUE 2147483647
MINVALUE 1;
CREATE SEQUENCE tabla1_id_seq START 4 INCREMENT 1 MAXVALUE 2147483647
MINVALUE 1;
CREATE TABLE tabla1 (id int4 DEFAULT nextval('tabla1_id_seq'::text)
PRIMARY KEY, descripcion varchar(80), borrado boolean DEFAULT 'f' NOT
NULL);
CREATE TABLE conceptos (id int4 DEFAULT
nextval('conceptos_id_seq'::text) PRIMARY KEY, id_tabla1 int CONSTRAINT
conceptos_ref_id REFERENCES tabla1(id) ON UPDATE cascade ON DELETE
restrict DEFERRABLE INITIALLY DEFERRED, descripcion varchar(80), borrado
boolean DEFAULT 'f' NOT NULL);
CREATE TABLE contador (id int4 DEFAULT nextval('contador_id_seq'::text)
PRIMARY KEY, cuenta int4 DEFAULT '1' NOT NULL);
[script end]
As you can see there is a REFERENCES keyword, so it should prohibit
inserting data into "conceptos" table without a "id_tabla1" key, which
is the reference to "tabla_1".
Now there are some insert statements:
[script continues]
INSERT INTO tabla1 (id, descripcion, borrado) VALUES ('0', 'Este es el
primer registro', 'f');
INSERT INTO tabla1 (id, descripcion, borrado) VALUES ('1', 'Este es el
segundo registro', 'f');
INSERT INTO tabla1 (id, descripcion, borrado) VALUES ('2', 'Este es el
tercer registro', 'f');
INSERT INTO tabla1 (id, descripcion, borrado) VALUES ('3', 'Este es el
cuarto registro', 'f');
INSERT INTO conceptos (id, id_tabla1, descripcion, borrado) VALUES ('0',
'', 'Este es el primer registro', 'f');
INSERT INTO conceptos (id, id_tabla1, descripcion, borrado) VALUES ('1',
'', 'Este es el segundo registro', 'f');
INSERT INTO conceptos (id, id_tabla1, descripcion, borrado) VALUES ('2',
'', 'Este es el tercer registro', 'f');
INSERT INTO contador (id, cuenta) VALUES ('0', '1');
[script end]
The "id_tabla1" field is left expreselly empty, but I think the
DEFERRABLE and INITIALLY DEFERRED keywords make this pass through. The
problem comes later:
[script continues]
CREATE OR REPLACE FUNCTION actualiza_cuenta()
RETURNS opaque
AS '
BEGIN
-- IF NEW.id_tabla = 0 THEN
-- RAISE EXECPTION ''El campo id_tabla1 ha de tener un
valor'';
-- END IF;
UPDATE contador SET cuenta = cuenta + 1 WHERE id = 1;
RETURN NEW;
END;
' LANGUAGE 'plpgsql';
CREATE TRIGGER actualiza_cuenta BEFORE INSERT OR UPDATE ON conceptos FOR
EACH row EXECUTE PROCEDURE actualiza_cuenta();
COMMIT;
[script end]
Note there are BEGIN and COMMIT statements in my script. This goes to
create the records without enforcing the referential integrity.
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)
I've read that referential integrity is implemented with "hidden"
triggers. Can PostgreSQL have more than one trigger for the same table?
If so, what is going on here?
I've tested without trigger and still creates the records.
I've read the Joel Burton page on referential integrity:
http://techdocs.postgresql.org/techdocs/hackingreferentialintegrity.php
What I'm doing wrong?
Manuel Cano
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?
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 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
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