Thread: Foreign Key Problem

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?


Thanks in advance..
Dennis

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.

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
>