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.
I create two tables, both with ID and NAME fields of type CHAR and populate
them with 2 records each, in
1 the ID and NAME are the same, in the other they aren't.
Then I create an intermediate table to allow the two to be associated with
each other, (to form a many-to-many
relationship) and populate it with the possible combinations.
If I then try to change the NAME column in either of the initial tables
where ithe NAME equals the ID I get
a foreign key violation, event though I'm pretty sure it shouldn't. An
example script is below.

Does anyone know why this is happening, and how to stop it?
Thanks in advance,
Stuart Hunt,
Senior Developer,
IdeaGen Software Limited

/* 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 (?)*/
update supplier set name="changed" where id="2"; /*succeeds*/
update product set name="4" where id="1"; /*also fails - violation of
fk2_prodsupp (?)*/
update product set name="changed" where id="2"; /*also succeeds*/

commit work;

/* end of script */