使用复合主键，如果为空则自动增量一列

发布于 2024-10-21 19:27:19 字数 401 浏览 1 评论 0原文

我的表：

CREATE TABLE methods (
    id INTEGER NOT NULL,
    version INTEGER NOT NULL,
    text TEXT NOT NULL,
    PRIMARY KEY (id,version)
    );

我希望列 id 在新行上为 null 时自动递增。我还希望将 version 列设置为 1。我希望将自动增量值存储在 sqlite_sequence 中。当插入带有 id 的行时，我希望 version 增加 1。

我在应用程序级别有这个，但在数据库级别需要它，并且了解我必须创建一两个触发器。

原文

My table:

CREATE TABLE methods (
    id INTEGER NOT NULL,
    version INTEGER NOT NULL,
    text TEXT NOT NULL,
    PRIMARY KEY (id,version)
    );

I would like the column id to auto-increment when null on a new row. I would also like the column version to be set to 1. I would like to store the auto-increment value in sqlite_sequence. When a row is inserted with id I would like version to increase by 1.

I have this at application level but need it at the database level and understand I will have to create a trigger or two.

分享到QQ

分享到微博