如何在sqlite中使用外键？ [英] How to use a foreign key in sqlite?

问题描述

我在sqlite中有两个表，它们与一个id连接。使用此表的应用程序正在Android OS上运行。

表1：

  | ID |条目1 | ENTRY2 | 
 | 1 | aaaaaa | aaaaa | 
 | 2 | bbbbbb | bbbbb | 
  

表2：

  | ID | entryx |恒| 
 | 1 | aaaaa | AAAAAAAA | 
 | 1 | baaaa | baaaaaaa | 
 | 1 | caaaa | caaaaaaa | 
 | 2 |阿巴巴| baabaaba | 
  

目前我使用下面的查询删除条目：

  do {
 db.delete（Table 1，id ='+ cid +'，null）; 
 db.delete（Table 2，id ='+ cid +'，null）; 
} 
 while（getNextID（））; 
  

我想使用一个外键，它允许我删除表1中的条目和所有条目在表2中也被删除。此外，我必须考虑表1中的数据插入表2之前，我怎样才能使用一个外键来做到这一点？该表使用id作为整数，它是主键，同样用于第二个表。

解决方案

正如selvin建议使用删除级联

http：// www .sqlite.org / foreignkeys.html

Table1

  CREATE TABLE table1（
 id PRIMARY KEY 
，entry1 text，entry2 text 
）; 
  

然后

 插入到table1值（1，aaaa，aaaaa）; 
插入到table1值（2，bbbb，bbbbb）; 
  

Table2

  CREATE TABLE table2（
 id int引用table1（id）ON DELETE CASCADE，条目x文本，常量文本
）; 
 
 
插入到table2值（1，aaaa，aaaaa）; 
插入到table2值（1，baaaa，baaaaaaa）; 
插入到table2值（1，caaaa，caaaaaaa）
插入到table2值（2，bbbb，bbbbb）; 
  

入场后的表格

 源码> select * from table1; 
 id entry1 entry2 
 ---------- ---------- ---------- 
 1 aaaa aaaaa 
 2 bbbb bbbbb 
 sqlite>从table2中选择* 
 id entryx常量
 ---------- ---------- ---------- 
 1 aaaa aaaaa 
 1 baaaa baaaaaaa 
 1 caaaa caaaaaaa 
 2 bbbb bbbbb 
  

删除

  sqlite>从table1中删除id = 1; 
  

删除后的表格

 源码>从table2中选择* 
 id entryx常量
 ---------- ---------- ---------- 
 2 bbbb bbbbb 
 
 
 sqlite> select * from table1; 
 id entry1 entry2 
 ---------- ---------- ---------- 
 2 bbbb bbbbb 
  

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  

这篇关于如何在sqlite中使用外键？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！