yoy - “Why-O-Why”

» frame | home browse filter search | refresh | log on

  printed zaterdag 25 mei 2013 2:03:10 from www.yoy.be

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

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?

location: Application Development > Data Storage > SQLite
created: 2/04/2011 0:03:07 « modified: 2/04/2011 0:04:27 (diff) weight: 0

children, tokens, references comments (1)

up down edit   del   created: 2/04/2011 0:03:17 « modified: 2/04/2011 0:03:17 weight: 0

~16ms [anonymous@yoy.be]