database - sqlite:具有自动增量列的多列主键

Question

我基本上想使用以下方案将表从 mysql 转换为 sqlite:

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

基本上，我希望每当我向表中插入任何内容时 ID 自动递增，VERSION 从 0 开始，但只要 VERSION 不同，仍然允许具有相同 ID 的多个项目。

我正在尝试使用 Sqlite 复制此行为，但是，我似乎无法创建表。似乎您只允许一列作为自动增量，并且它必须是主键。如果我将 ID 设为主键，那么我就不能将 VERSION 用作键的一部分。但是，如果我制作一个多列键 (ID, VERSION) ，那么我无法获得自动递增的 ID。

是否有解决方法或更好的方法来设计我的表格？

---

我想到了以下解决方案:

表 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。

Answer 1

不幸的是没有这样的特性，它只存在于 AFAIK 中。

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

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

这对我有用。

您还可以创建一个触发器来正确更新版本:

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;

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

> 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 开始。你可以修补为了得到一些不同的东西。