Autogenerate Composite Key In Sqlite

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-g

Solution 1:

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).

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.

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.

Something like:

CREATETABLE stores(store_id INTEGERPRIMARY KEY
                  , address TEXT
                    -- etc
                   );
CREATETABLE product(prod_id INTEGERPRIMARY KEY
                   , name TEXT
                     -- etc
                   );
CREATETABLE inventory(store_id INTEGERREFERENCES stores(store_id)
                     , prod_id INTEGERREFERENCES product(prod_id)
                     , PRIMARY KEY(store_id, prod_id)) WITHOUT ROWID;