我在sqlite中有两个表,它们的ID是“连接”的。使用此表的应用程序正在Android OS上运行。
表格1;:
|id| entry 1| entry2|
|1 | aaaaaa | aaaaa |
|2 | bbbbbb | bbbbb |
表2:
|id| entryx| constant|
|1 | aaaaa | aaaaaaaa|
|1 | baaaa | baaaaaaa|
|1 | caaaa | caaaaaaa|
|2 | ababa | baabaaba|
目前,我使用循环通过以下查询删除条目:
do{
db.delete("Table 1","id='"+cid+"'",null);
db.delete("Table 2","id='"+cid+"'",null);
}
while(getNextID());
我想使用外键,这使我可以删除表1中的条目,并且表2中的所有条目也将被删除。此外,我必须考虑在将表1中的数据插入表2中之前将其插入。如何使用外键来执行此操作?该表使用id作为int,它是主键,第二个表也使用相同的键。
正如塞尔文建议在删除级联上使用
http://www.sqlite.org/foreignkeys.html
表格1
CREATE TABLE table1 (
id PRIMARY KEY
,entry1 text,entry2 text
);
然后
insert into table1 values(1,"aaaa","aaaaa");
insert into table1 values(2,"bbbb","bbbbb");
表2
CREATE TABLE table2(
id int references table1(id) ON DELETE CASCADE, entryx text, constant text
);
insert into table2 values(1,"aaaa","aaaaa");
insert into table2 values(1," baaaa ","baaaaaaa");
insert into table2 values(1," caaaa ","caaaaaaa")
insert into table2 values(2,"bbbb","bbbbb");
输入后的表格
sqlite> select * from table1;
id entry1 entry2
---------- ---------- ----------
1 aaaa aaaaa
2 bbbb bbbbb
sqlite> select * from table2;
id entryx constant
---------- ---------- ----------
1 aaaa aaaaa
1 baaaa baaaaaaa
1 caaaa caaaaaaa
2 bbbb bbbbb
删除
sqlite> delete from table1 where id=1;
删除后的表
sqlite> select * from table2;
id entryx constant
---------- ---------- ----------
2 bbbb bbbbb
sqlite> select * from table1;
id entry1 entry2
---------- ---------- ----------
2 bbbb bbbbb
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句