SQLite, a foreign key and its referential integrity...

2011-04-02 00:03  i2963    [permalink]

Hmm, I guess this will take a lot more getting used to than I thought... Consider this code:

create table tbl1 (
id integer primary key autoincrement,
name varchar(50) not null
);
create table tbl2 (
id integer primary key autoincrement,
tbl1_id int not null,
name varchar(50) not null,
constraint FK_tbl1_tbl2 foreign key (tbl1_id) references tbl1 (id)
);
insert into tbl1 (id,name) values (1,'test1');
insert into tbl2 (id,tbl1_id,name) values (2,1,'test2');
insert into tbl2 (id,tbl1_id,name) values (3,2,'test3');
update tbl1 set id=id+100;
select * from tbl1;
select * from tbl2;

This works on SQLite3 without errors! Not on the inserts, setting an autoincrement field, not on updating the id field(s) that are in use by a foreign key, which actually breaks referential integrity between the two tables! Or have I stumbled upon a fault/bug/incorrectness in SQLite3?

→