是否有可能(模仿?在 Sqlite 中的复合 PK 上自动增量

Question

根据SQLite文档，获取自动增量列的唯一方法是在主键上。

我需要一个复合主键，但我也需要自动递增。 有没有办法在SQLite中实现这两个目标？

我的表的相关部分，因为我会在PostgreSQL中编写它：

CREATE TABLE tstage (
    id                   SERIAL NOT NULL,
    node                 INT REFERENCES nodes(id) NOT NULL,
    PRIMARY KEY (id,node),
    -- ... other columns
);

此要求的原因是所有节点最终都会将其数据转储到单个集中式节点，在该节点中，使用单列 PK 时，将发生冲突。

Answer 1

文档是正确的。但是，可以在触发器中重新实现自动增量逻辑：

CREATE TABLE tstage (
    id    INT,  -- allow NULL to be handled by the trigger
    node  INT REFERENCES nodes(id) NOT NULL,
    PRIMARY KEY (id, node)
);
CREATE TABLE tstage_sequence (
    seq INTEGER NOT NULL
);
INSERT INTO tstage_sequence VALUES(0);
CREATE TRIGGER tstage_id_autoinc
AFTER INSERT ON tstage
FOR EACH ROW
WHEN NEW.id IS NULL
BEGIN
    UPDATE tstage_sequence
    SET seq = seq + 1;
    UPDATE tstage
    SET id = (SELECT seq
              FROM tstage_sequence)
    WHERE rowid = NEW.rowid;
END;

(如果有多个表，请使用具有表名的通用my_sequence表。

Answer 2

触发器

有效，但很复杂。更简单地说，您可以避免串行 ID。一种方法是使用 GUID。不幸的是，我找不到让 SQLite 默认为您生成 GUID 的方法，因此您必须在应用程序中生成它。也没有 GUID 类型，但可以将其存储为字符串或二进制 blob。

或者，也许您的其他列中有一些内容可以用作合适的键。如果您知道插入不会比您选择的时间戳格式的解析更频繁地发生(SQLite 提供了几种，请参阅第 1.2 节(，那么也许(node, timestamp_column)是一个很好的主键。

或者，您可以使用SQLite的AUTOINCREMENT，但通过sqlite_sequence表在每个节点上设置起始编号，以便生成的序列号不会发生冲突。由于 rowid SQLite 是一个 64 位数字，因此您可以通过为每个节点生成一个唯一的 32 位数字(IP 地址是一个方便的，可能是唯一的 32 位数字(并将其向左移动 32 位，或者等效地将其乘以 4294967296 来做到这一点。因此，64位rowid实际上变成了两个串联的32位数字，NODE_ID, RECORD_ID，保证除非一个节点生成超过40亿条记录，否则不会发生冲突。

Answer 3

怎么样...

假设

• 只需要PK中的唯一性，而不是顺序性
• 源表具有 PK

创建带有一个额外列的中央表，节点号...

CREATE TABLE tstage (
    node  INTEGER NOT NULL,
    id    INTEGER NOT NULL,   <<< or whatever the source table PK is
    PRIMARY KEY (node, id)
       :
);

将数据汇总到集中式节点时，将源节点的编号插入"node"，并将"id"设置为源表的主键列值...

INSERT INTO tstage (nodenumber, sourcetable_id, ...);

无需在中央表上维护另一个自动递增列，因为 nodenumber+sourcetable_id 将始终是唯一的。