Referencial integrity when there are timestamp primary keys

Started by Vilson fariasover 25 years ago3 messagesbugs
Jump to latest
#1Vilson farias
vilson.farias@digitro.com.br

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

#2Stephan Szabo
sszabo@megazone23.bigpanda.com
In reply to: Vilson farias (#1)
Re: Referencial integrity when there are timestamp primary keys

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:

Show quoted text

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.

Attachments:

fk2.patchtext/plain; charset=US-ASCII; name=fk2.patchDownload+9-8
#3Bruce Momjian
bruce@momjian.us
In reply to: Stephan Szabo (#2)
Re: Referencial integrity when there are timestamp primary keys

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