Helen,
I'm probably missing something here, but my understanding of this was that
the FK relationship is on the
ID columns. We aren't trying to modify the ID columns, we're trying to
modify the NAME columns, so surely
the foreign key shouldn't kick in? Also, this only happens when the NAME
field is the sam as the ID field.
Thanks,
Stuart

-----Original Message-----
From: Helen Borrie [mailto:helebor@...]
Sent: Tuesday, 11 September 2001 03:33 PM
To: IBObjects@yahoogroups.com
Subject: Re: [IBO] Problem with InterBase 5.6


At 03:13 PM 11-09-01 +0100, you wrote:

>Dear all, I know this is a bit off topic, but I'm having a problem with
>InterBase 5.6 and spurious foreign key violations.

No, they aren't spurious. They are working as expected to prevent violation
of the referential integrity between the two master tables and the
intersection table.

>/* start of script */
>
>create table supplier
>(
>id char (12) not null,
>name char (40) not null,
>constraint pk_supplier primary key (id)
>);
>
>create unique asc index suppliernameindx on supplier (name);
>
>commit work;
>
>create table product (
>id char (12) not null,
>name char (40) not null,
>constraint pk_product primary key (id)
>);
>
>create unique asc index productnameindx on product (name);
>
>commit work;
>
>create table prodsupp
>(
>supplierid char (12) not null,
>productid char (12) not null,
>constraint pk_prodsupp primary key (supplierid, productid)
>);
>
>alter table prodsupp
>add constraint fk1_prodsupp foreign key (supplierid) references supplier;
>
>alter table prodsupp
>add constraint fk2_prodsupp foreign key (productid) references product;
>
>commit work;
>
>insert into supplier values ("1", "1");
>insert into supplier values ("2", "different");
>insert into product values ("1", "1");
>insert into product values ("2", "different");
>insert into prodsupp values ("1", "1");
>insert into prodsupp values ("2", "2");
>
>commit work;
>
>update supplier set name="3" where id="1"; /* fails - violation of
>fk1_prodsupp (?)*/

violation of the foreign key constraint - prodsupp would be left with the
first record referencing nothing in supplier table

>update supplier set name="changed" where id="2"; /*succeeds*/

ok 'cuz name doesn't have a referential relationship

>update product set name="4" where id="1"; /*also fails - violation of
>fk2_prodsupp (?)*/

same problem as above.

>update product set name="changed" where id="2"; /*also succeeds*/

ok 'cuz name doesn't have a referential relationship

>commit work;
>
>/* end of script */

H.

All for Open and Open for All
InterBase Developer Initiative · http://www.interbase2000.org
_______________________________________________________




Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/