From: | Dennis <dennis(at)zserve(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Foreign Key Problem |
Date: | 2001-06-07 23:06:48 |
Message-ID: | 9fp1e8$153g$1@news.tht.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Phuong Ma | 2001-06-07 23:07:04 | WAL article |
Previous Message | Stephan Szabo | 2001-06-07 22:35:51 | Re: Foreign Key Problem |