Re: Foreign Key Problem

On Thu, 7 Jun 2001, Dennis Muhlestein wrote:

> Is This s bug:
>
>
> create table A
> (
> key varchar(20) not null primary key
> );
>
> create table B
> (
> id serial not null primary key,
> col1 varchar(20) not null,
> col2 varchar(20) not null
> );
>
> alter table B create constraint fk_col1 foreign key ( col1 ) references A (
> key ) on delete cascade on update cascade;
>
> alter table B create constraint fk_col2 foreign key ( col2 ) references A (
> key ) on delete cascade on update cascade;
>
>
> SQL Creation and operation works fine. In the case that one row in table B
> where col1 = col2, if I update A.key, I'll get a referential integrity
> violation??
>
> ie: ERROR: fk_col1 referential integrity violation - key referenced from B
> not found in A.
>
> Is there a way to make this type of constraint work with the update?
>
> If not, is there a way to create a constraint so that col1 != col2 is
> inforced?

I believe this is a bug. I'm pretty sure that its an issue of the first
update seeing an invalid state. What I'm working on now may make this
work (I'm doing some work to the triggers now -- checking that the state
change hasn't been undone by some later change between time of action
and time of check).

I'm not sure of a work around in the meantime.