Skip site navigation (1) Skip section navigation (2)

Referencial integrity when there are timestamp primary keys

From: "Vilson farias" <vilson(dot)farias(at)digitro(dot)com(dot)br>
To: <pgsql-bugs(at)postgresql(dot)org>
Subject: Referencial integrity when there are timestamp primary keys
Date: 2000-10-06 20:29:04
Message-ID: 004201c02fd4$12ad66a0$98a0a8c0@dti.digitro.com.br (view raw or flat)
Thread:
Lists: pgsql-bugs
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



Responses

pgsql-bugs by date

Next:From: Srinivas VallurupalliDate: 2000-10-06 23:39:13
Subject: Error in creating field in a table
Previous:From: Wendel LeibeDate: 2000-10-06 18:31:21
Subject: Possible bug with permissions. (Table A has select , insert, update rights but has a reference to a second table that only has select rights)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group