Referential integrity doesn't work? - Mailing list pgsql-general

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