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
_______________________________________________________