Fw: Referencial integrity when there are timestamp primary keys - Mailing list pgsql-general

----- Original Message -----
From: Vilson farias <vilson.farias@digitro.com.br>
To: <pgsql-bugs@postgresql.org>
Sent: Sexta-feira, 6 de Outubro de 2000 18:29
Subject: Referencial integrity when there are timestamp primary keys


 I can only set a referencial integrity between these tables when there are
 no data, even if there are no possible referential integrity violation.
It's strange, but this error only happens when I'm using a primary key
containing timestamp fields.

 Please analyse the following case.

 teste=# CREATE TABLE E_2 (
 teste(#        codigo2              integer NOT NULL,
 teste(#        dt_inicio            datetime NOT NULL,
 teste(#        CONSTRAINT XPKE_2 PRIMARY KEY (codigo2, dt_inicio)
 teste(#
 teste(# );
 NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index 'xpke_2' for
 table 'e_2'
 CREATE
 teste=# CREATE TABLE E_1 (
 teste(#        codigo1              integer NOT NULL,
 teste(#        dt_inicial           datetime NOT NULL,
 teste(#        valor                varchar(20),
 teste(#        CONSTRAINT XPKE_1 PRIMARY KEY (codigo1, dt_inicial)
 teste(#
 teste(# );
 NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index 'xpke_1' for
 table 'e_1'
 CREATE
 teste=# CREATE TABLE E_3 (
 teste(#        codigo3              serial NOT NULL,
 teste(#        codigo1              integer,
 teste(#        dt_inicial           datetime,
 teste(#        codigo2              integer,
 teste(#        dt_inicio            datetime,
 teste(#        CONSTRAINT XPKE_3 PRIMARY KEY (codigo3)
 teste(#
 teste(# );
 NOTICE:  CREATE TABLE will create implicit sequence 'e_3_codigo3_seq' for
 SERIAL column 'e_3.codigo3'
 NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index 'xpke_3' for
 table 'e_3'
 CREATE
 teste=# insert into e_1 values (1, '2000-10-06 10:00:00', 'll');
 INSERT 445181 1
 teste=# insert into e_2 values (2, '2000-10-06 11:00:00');
 INSERT 445182 1
 teste=# insert into e_3 values (1,1,'2000-10-06 10:00:00',2,'2000-10-06
 11:00:00');
 INSERT 445183 1
 teste=# ALTER TABLE E_3
 teste-#        ADD CONSTRAINT R_3 FOREIGN KEY (codigo2, dt_inicio)
 teste-#                              REFERENCES E_2;
 NOTICE:  ALTER TABLE ... ADD CONSTRAINT will create implicit trigger(s) for
 FOREIGN KEY check(s)
 ERROR:  <unnamed> referential integrity violation - key referenced from e_3
 not found in e_2
 teste=# ALTER TABLE E_3
 teste-#        ADD CONSTRAINT R_2 FOREIGN KEY (codigo1, dt_inicial)
 teste-#                              REFERENCES E_1;
 NOTICE:  ALTER TABLE ... ADD CONSTRAINT will create implicit trigger(s) for
 FOREIGN KEY check(s)
 ERROR:  <unnamed> referential integrity violation - key referenced from e_3
 not found in e_1
 teste=# delete from e_3;
 DELETE 1
 teste=# ALTER TABLE E_3
 teste-#        ADD CONSTRAINT R_2 FOREIGN KEY (codigo1, dt_inicial)
 teste-#                              REFERENCES E_1;
 NOTICE:  ALTER TABLE ... ADD CONSTRAINT will create implicit trigger(s) for
 FOREIGN KEY check(s)
 CREATE

 CREATE TABLE E_2 (
        codigo2              integer NOT NULL,
        dt_inicio            datetime NOT NULL,
        CONSTRAINT XPKE_2 PRIMARY KEY (codigo2, dt_inicio)

 );


 CREATE TABLE E_1 (
        codigo1              integer NOT NULL,
        dt_inicial           datetime NOT NULL,
        valor                varchar(20),
        CONSTRAINT XPKE_1 PRIMARY KEY (codigo1, dt_inicial)

 );


 CREATE TABLE E_3 (
        codigo3              serial NOT NULL,
        codigo1              integer,
        dt_inicial           datetime,
        codigo2              integer,
        dt_inicio            datetime,
        CONSTRAINT XPKE_3 PRIMARY KEY (codigo3)

 );


 ALTER TABLE E_3
        ADD CONSTRAINT R_3 FOREIGN KEY (codigo2, dt_inicio)
                              REFERENCES E_2;


 ALTER TABLE E_3
        ADD CONSTRAINT R_2 FOREIGN KEY (codigo1, dt_inicial)
                              REFERENCES E_1;


 Best regards,

 José Vilson de Mello de Farias
 Dígitro Tecnologia Ltda - Brazil