在SQLite中自动生成复合键 [英] Autogenerate composite key in SQLite

问题描述

我有一个用于SQLite的复合主键{shop_id，product_id} 现在，我想要product_id的自动递增值，如果更改商店ID，该值将重置为1.基本上，我想要自动生成的复合密钥 例如

I have a composite primary key {shop_id, product_id} for SQLite Now, I want an auto-increment value for product_id which resets to 1 if shop id is changed. Basically, I want auto-generated composite key e.g.

商店ID产品ID

1 1

1 2

1 3

2 1

2 2

3 1

我可以通过自动增量实现吗?怎么样?

Can I achieve this with auto-increment? How?

推荐答案

普通Sqlite表是B *树，它们使用64位整数作为其键.这称为 rowid .插入行时，如果未明确为此指定值，则会生成一个值. INTEGER PRIMARY KEY列充当此rowid的别名.与名称相反，只能在所说的INTEGER PRIMARY KEY列上使用的AUTOINCREMENT关键字只是改变了该rowid的计算方式-如果您省略一个值，则无论该关键字是否存在，都会创建一个值，因为它实际上是rowid，并且必须有一个数字.详细信息此处. (行值通常以递增的顺序(但不一定是顺序的顺序)生成，不应将其视为行号或类似的东西，顺便说一句).

Normal Sqlite tables are B*-trees that use a 64-bit integer as their key. This is called the rowid. When inserting a row, if a value is not explicitly given for this, one is generated. An INTEGER PRIMARY KEY column acts as an alias for this rowid. The AUTOINCREMENT keyword, which can only be used on said INTEGER PRIMARY KEY column, contrary to the name, merely alters how said rowid is calculated - if you leave out a value, one will be created whether that keyword is present or not, because it's really the rowid and must have a number. Details here. (rowid values are generally generated in increasing, but not necessarily sequential, order, and shouldn't be treated like a row number or anything like that, btw).

除单个INTEGER列以外的任何主键均被视为唯一索引，而rowid仍为真正的主键(除非它是

Any primary key other than a single INTEGER column is treated as a unique index, while the rowid remains the true primary key (Unless it's a WITHOUT ROWID table), and is not autogenerated. So, no, you can't (easily) do what you want.

我可能会设计出一个数据库设计，其中有一个商店表，一个产品表(每个表都有自己的ID)以及一个在两者之间建立多对多关系的联结表.这样可以使商店之间的产品ID保持相同，这对人们而言可能会减少混乱-例如，我不希望同一商品在同一链的两个不同商店中具有不同的SKU.

I would probably work out a database design where you have a table of shops, a table of products, each with their own ids, and a junction table that establishes a many-to-many relation between the two. This keeps the product id the same between stores, which is probably going to be less confusing to people - I wouldn't expect the same item to have a different SKU in two different stores of the same chain, for instance.

类似的东西:

CREATE TABLE stores(store_id INTEGER PRIMARY KEY
                  , address TEXT
                    -- etc
                   );
CREATE TABLE product(prod_id INTEGER PRIMARY KEY
                   , name TEXT
                     -- etc
                   );
CREATE TABLE inventory(store_id INTEGER REFERENCES stores(store_id)
                     , prod_id INTEGER REFERENCES product(prod_id)
                     , PRIMARY KEY(store_id, prod_id)) WITHOUT ROWID;

这篇关于在SQLite中自动生成复合键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！