Re: Foreign Key Problem

Thanks,

For right now, we'll get around the problem by adding a
check to table B

        col2 varchar(20) not null check ( col2 != col1 )

Then the foreign key will never have a problem.

-Dennis

Stephan Szabo wrote:

> 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.
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>