在其他表中使用INT PRIMARY KEY值，同一数据库调用

I have created the following tables in SQLite3 to tag items (after reading this great response). The tags are saved in the table Tags and the ItemTags will show the relation between one item (from the table Items) and one or more tags (from the table Tags).

我在SQLite3中创建了以下表来标记项目(在阅读了这个很棒的响应之后)。标签保存在表格中,而ItemTags将显示一个项目(来自表格项目)和一个或多个标签(来自表格标签)之间的关系。

CREATE TABLE Items ItemID INTEGER PRIMARY KEY, Title TEXT, Comment TEXT;    
CREATE TABLE Tags TagID INTEGER PRIMARY KEY, Title TEXT;
CREATE TABLE ItemsTags ItemID INTEGER, TagID INTEGER;

When submitting a new row, the user will enter a title and a comment (which will be saved in the table Items) and chose from one or more tags (which are chosen/added from/to the table Tags). So far, I've for instance managed to do this:

提交新行时,用户将输入标题和注释(将保存在表项中),并从一个或多个标签中选择(从表格中选择/添加标签)。到目前为止,我已经设法做到了这一点:

INSERT INTO Items (Title, Comment) VALUES ('First title', 'First comment');

I want the column ItemID to be a INTEGER PRIMARY KEY, but at the same time, I want to access that value in the same call. Say, for instance, that my table Tags has the following layout:

我希望列ItemID为INTEGER PRIMARY KEY,但同时,我想在同一个调用中访问该值。比方说,我的表格标签有以下布局:

TagID | Title
------|----------
    1 | First tag
    2 | Second tag

and that I want to tag the above mentioned statement ("First title", which has the TitleID 1) with TagID 1 and 2, and save the relation to the table ItemsTags. After I'm done, I want the following changes to be made:

并且我想用TagID 1和2标记上面提到的语句(“First title”,其标题ID为1),并将关系保存到表ItemsTags。完成后,我希望进行以下更改:

Table: Items

TitleID | Title       | Comment
--------|-------------|--------------
      1 | First title | First comment

Table: Tags

TagID | Title
------|----------
    1 | First tag
    2 | Second tag

Table: ItemsTags

TagID | ItemID
------|---------
    1 | 1
    2 | 1

How can I achieve this? Thanks in advance!

我怎样才能做到这一点?提前致谢!

1 个解决方案

#1

You cannot insert rows into two separate tables with a single call to the database, nor can you insert two rows into the same table with a single call. You will need four in this case:

您不能通过一次调用数据库将行插入两个单独的表中,也不能通过一次调用将两行插入同一个表中。在这种情况下,您将需要四个:

 INSERT INTO Items (Title, Comment) VALUES ('First title', 'First comment');
 SELECT last_insert_rowid() -- To get last inserted id
 INSERT INTO  ItemTags (TagID, ItemID) VALUES (1, :LastID)
 INSERT INTO  ItemTags (TagID, ItemID) VALUES (2, :LastID)

If you place them all inside a transaction, they will all get committed at the same time, with only a single lock placed on the database file.

如果将它们全部放在事务中,它们将同时被提交,只有一个锁放在数据库文件中。

#1