I have two tables in sqlite, which are "connected" with an id. The app using this tables is running on Android OS.
Table 1;:
|id| entry 1| entry2|
|1 | aaaaaa | aaaaa |
|2 | bbbbbb | bbbbb |
Table 2:
|id| entryx| constant|
|1 | aaaaa | aaaaaaaa|
|1 | baaaa | baaaaaaa|
|1 | caaaa | caaaaaaa|
|2 | ababa | baabaaba|
At the moment I delete entries with the following query, by using a loop:
do{
db.delete("Table 1","id='"+cid+"'",null);
db.delete("Table 2","id='"+cid+"'",null);
}
while(getNextID());
I want to use a foreign key, which allows me to delete the entry in table 1 and all entries in table 2 are deleted as well. Besides, I have to consider that the data in table 1 are inserted, before they are inserted in table 2. How can I use a foreign key to do this? The table is using id as int and it is the primary key, the same is used for the second table.
As selvin suggested use on delete cascade
http://www.sqlite.org/foreignkeys.html
Table1
CREATE TABLE table1 (
id PRIMARY KEY
,entry1 text,entry2 text
);
Then
insert into table1 values(1,"aaaa","aaaaa");
insert into table1 values(2,"bbbb","bbbbb");
Table2
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");
Tables after entry
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
Delete
sqlite> delete from table1 where id=1;
Tables after delete
sqlite> select * from table2;
id entryx constant
---------- ---------- ----------
2 bbbb bbbbb
sqlite> select * from table1;
id entry1 entry2
---------- ---------- ----------
2 bbbb bbbbb