Re: Fw: Referencial integrity when there are timestamp primary keys

I see this as well on my CVS machine. I'll look to see why it's
doing that this week and 7.1 should hopefully have this fixed.
It's especially wierd since making the constraint and then putting
the same data in seems to work.

Stephan Szabo
sszabo(at)bigpanda(dot)com

On Mon, 9 Oct 2000, Vilson farias wrote:

> 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;