在SQLite中使用外键从表中插入和查询数据 [英] Inserting and querying data from table with foreign key in SQLite

问题描述

我想创建两个相关的表.我正在创建表的查询如下:

I wanted to create two related tables. My query that is creating tables looks like this:

static final String SQL_CREATE_TABLE_LISTS = 
"CREATE TABLE Lists(Id INTEGER PRIMARY KEY AUTOINCREMENT, Name TEXT);";
static final String SQL_CREATE_TABLE_ITEMS = 
"CREATE TABLE Items(IdList INTEGER, ItemName TEXT, FOREIGN KEY(IdList) REFERENCES Lists(Id));";

我现在想从表Items中插入并选择一些数据，但是我不知道查询的外观.可以说我在表列表中有一条记录:id = 1和name = element1.现在我想将3条记录添加到表Items中，因此IdList将为1，名称将为item1，item2和item3.插入查询将如何?然后，如果我想接受前任.表Ids为1的表Item中的所有名称.select查询将如何?谢谢.

I want now insert and select some data from table Items, but I do not know how the query should looks like. Lets say I have one record in table Lists: id=1 and name=element1. And now I want to add 3 records to table Items, so IdList will be 1, and name will be item1, item2 and item3. How the inserting query will be like? And then, if I want to take for ex. all Names from table Items that its IdList is 1, how the select query will be like? Thanks.

推荐答案

FOREIGN KEY是一个约束(必须遵循的规则)，它没有定义用于提取数据的关系车间/链接.换句话说，这表示如果不符合规则，则无法插入行.并不是说每次访问任何一个表都将它们自动链接.

A FOREIGN KEY is a Constraint (Rule that MUST be followed), it does not define a relationshop/link for extracting data. In other words it is saying if the rule is not met then a row cannot be inserted. It is not saying every time you access either of the tables that they are automatically linked.

插入时，您将首先插入列表，然后使用(检查)可用列表插入项目.您不能直接插入/跨多个表.

When inserting you you would insert the Lists first, you would then insert the Items using(checking) the available Lists. You cannot insert into/across multiple tables directly.

查询数据时需要使用JOIN，因为FOREIGN KEY只是插入行时要检查的一条规则(约束).

You need to use JOIN when querying the data as FOREIGN KEY is just a rule (constraint) that is checked when inserting a row.

因此，您将按照以下方式进行操作:-

So you would do something along the line of:-

SELECT Lists.Id, Lists.Name, Items.ItemName FROM Lists JOIN Items ON Lists.Id = Items.IdList

这篇关于在SQLite中使用外键从表中插入和查询数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！