Thread

1. Referencial integrity when there are timestamp primary keys

   Vilson farias <vilson.farias@digitro.com.br> — 2000-10-06T20:29:04Z

   I found a irregular behavior with constraints.
   
   I can only set a referencial integrity between these tables when there are
   no data, even if there are no change to referential integrity violation.
   
   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
   
   
   
   

2. Re: Referencial integrity when there are timestamp primary keys

   Stephan Szabo <sszabo@megazone23.bigpanda.com> — 2000-10-10T17:04:56Z

   This is a stupid garden variety bug and I'm not sure why I didn't catch
   it previously.  The patch included is against fairly current sources, but
   it may apply cleanly against 7.0.2 as well.
   
   On Fri, 6 Oct 2000, Vilson farias wrote:
   
   > I found a irregular behavior with constraints.
   > 
   > I can only set a referencial integrity between these tables when there are
   > no data, even if there are no change to referential integrity violation.
   

3. Re: Referencial integrity when there are timestamp primary keys

   Bruce Momjian <pgman@candle.pha.pa.us> — 2000-10-10T17:13:25Z

   Applied.  Thanks.
   
   > 
   > This is a stupid garden variety bug and I'm not sure why I didn't catch
   > it previously.  The patch included is against fairly current sources, but
   > it may apply cleanly against 7.0.2 as well.
   > 
   > On Fri, 6 Oct 2000, Vilson farias wrote:
   > 
   > > I found a irregular behavior with constraints.
   > > 
   > > I can only set a referencial integrity between these tables when there are
   > > no data, even if there are no change to referential integrity violation.
   Content-Description: 
   
   [ Attachment, skipping... ]
   
   
   -- 
     Bruce Momjian                        |  http://candle.pha.pa.us
     pgman@candle.pha.pa.us               |  (610) 853-3000
     +  If your life is a hard drive,     |  830 Blythe Avenue
     +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026