sqlite：具有自动增量列的多列主键 [英] sqlite: multi-column primary key with an auto increment column

问题描述

我基本上想要将一个表从mysql转换为sqlite与以下方案：

I basically want to convert a table from mysql to sqlite with the following scheme:

create table items (
id integer auto_increment,
version integer default 0,
primary key (id, version)
);

基本上，我想让ID在表中插入任何内容时自动递增，VERSION从0，但是仍然允许多个项目具有相同的ID，只要VERSION不同。

Essentially, I want ID to auto increment whenever I insert anything into the table, with VERSION starting off at 0, but still allow multiple items with the same ID as long as VERSION is different.

我试图复制这个行为与Sqlite然而，我似乎不能以获得表创建工作。看起来你只允许一列作为autoincrement，它必须是主键。如果我使ID为主键，那么我不能使用VERSION作为键的一部分。但是，如果我创建一个多列键（ID，VERSION），则我无法获取ID自动递增。

I'm trying to replicate this behavior with Sqlite however, I can't seem to get table creation working. It seems like you are only allowed one column as autoincrement and it has to be the primary key. If I make ID the primary key, then I can't use VERSION as part of the key. However, if I make a multi-column key (ID, VERSION) , then I can't get ID to auto increment.

有一个解决方法或者更好设计我的表的方式？

Is there a workaround or perhaps a better way of designing my table?

我在想以下解决方案：

表1：

create table items {
id integer primary autoincrement,
version integer}

表2：

create table item_version {
id integer,
version integer,
primary key (id, version)
}

当我添加一个新项目，我添加到项目，并有ID自动增量。但是，如果我有一个新版本的相同的id，我将它添加到item_version。基本上，我使用item_version来保存除了项目之外的所有项目，只是为了生成唯一的ID。

When i add a new item, i add it to items and have ID auto increment. however, if i ever have a new version of the same id, i add it to item_version. basically, i use item_version to hold everything but items to just to generate unique ids.

推荐答案

不幸的是没有这样的功能，只在MySQL中存在AFAIK。

Unfortunately there is no such feature, it exists AFAIK only in MySQL.

当我需要类似的东西时，我只是在插入新行时使用以下SQL：

When I need something like that I just used following SQL when inserting new rows:

INSERT INTO items(id,version) 
SELECT new_id,SELECT COALESCE(MAX(version),0)+1 FROM items WHERE id=new_id)

这对我有用。

触发器将正确更新版本：

You can also create a trigger that would update the version correctly:

create trigger my_increment
after insert 
on items 
begin 
    update items 
    set version=(select max(version) from items where id=new.id)+1 
    where id=new.id and version = 0;
end;

现在，每次在表中插入一个新值时：

Now, each time you insert a new value into table:

> insert into items(id) values(10);
> insert into items(id) values(15);
> insert into items(id) values(10);
> select * from items;
10|1
15|1
10|2

您可以看到它为新插入的ID创建版本。从1开始。你可以修改
一点，以得到不同的东西。

As you can see it creates version for newly inserted id. Starting from 1. You can tinker with it a little in order to get something different.

这篇关于sqlite：具有自动增量列的多列主键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！