Styjun

What is the meaning of "понаехать"?

Do I have to explain the mechanical superiority of the player-character within the fiction of the game?

Second 100 amp breaker inside existing 200 amp residential panel for new detached garage

Overloading operator[] and NOT getting "lvalue required as left operand of assignment" error

How can I prevent a user from copying files on another hard drive?

Is there a name for the trope when there is a moments dialogue when someone pauses just before they leave the room?

I found a password with hashcat, but it doesn't work

Can I enter the UK for 24 hours from a Schengen area, holding an Indian passport?

Is there any proof that high saturation and contrast makes a picture more appealing in social media?

What constitutes a syllable?

Why is oilcloth made with linseed oil?

Should the party get XP for a monster they never attacked?

How did the Vostok ejection seat safely eject an astronaut from a sealed space capsule?

What is "industrial ethernet"?

Is there a difference between an NFC and RFID chip?

Print one file per line using echo

Improve appearance of the table in Latex

Boss wants someone else to lead a project based on the idea I presented to him

A word for delight at someone else's failure?

Why is it easier to balance a non-moving bike standing up than sitting down?

I just entered the USA without passport control at Atlanta airport

Is there a term for the belief that "if it's legal, it's moral"?

Dmesg full of I/O errors, smart ok, four disks affected

Novel in which alien (Martian?) is trapped on Earth in prehistory

Autogenerate composite key in SQLite

How to list the tables in a SQLite database file that was opened with ATTACH?Any good ORM tools for Android development?SQLite - UPSERT *not* INSERT or REPLACESqlite primary key on multiple columnsHow can I define a composite primary key in SQL?How do I check in SQLite whether a table exists?SQLite Reset Primary Key FieldImprove INSERT-per-second performance of SQLite?How to properly create composite primary keys - MYSQLALTER TABLE to add a composite primary key

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty height:90px;width:728px;box-sizing:border-box;

2

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.

Shop ID Product Id

1 1

1 2

1 3

2 1

2 2

3 1

Can I achieve this with auto-increment? How?

sqlite android-sqlite composite-primary-key

share|improve this question

asked Mar 25 at 7:07

VrishankVrishank

15714

add a comment | 

2

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.

Shop ID Product Id

1 1

1 2

1 3

2 1

2 2

3 1

Can I achieve this with auto-increment? How?

sqlite android-sqlite composite-primary-key

share|improve this question

asked Mar 25 at 7:07

VrishankVrishank

15714

add a comment | 

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.

Shop ID Product Id

1 1

1 2

1 3

2 1

2 2

3 1

Can I achieve this with auto-increment? How?

sqlite android-sqlite composite-primary-key

share|improve this question

asked Mar 25 at 7:07

VrishankVrishank

15714

share|improve this question

asked Mar 25 at 7:07

VrishankVrishank

15714

share|improve this question

asked Mar 25 at 7:07

VrishankVrishank

15714

asked Mar 25 at 7:07

VrishankVrishank

15714

asked Mar 25 at 7:07

VrishankVrishank

15714

1 Answer
1

active

oldest

votes

3

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:

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;

share|improve this answer

edited Mar 25 at 7:46

answered Mar 25 at 7:40

ShawnShawn

7,2902716

add a comment | 

Your Answer

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55332718%2fautogenerate-composite-key-in-sqlite%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

3

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:

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;

share|improve this answer

edited Mar 25 at 7:46

answered Mar 25 at 7:40

ShawnShawn

7,2902716

add a comment | 

3

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:

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;

share|improve this answer

edited Mar 25 at 7:46

answered Mar 25 at 7:40

ShawnShawn

7,2902716

add a comment | 

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:

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;

share|improve this answer

edited Mar 25 at 7:46

answered Mar 25 at 7:40

ShawnShawn

7,2902716

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;

share|improve this answer

edited Mar 25 at 7:46

answered Mar 25 at 7:40

ShawnShawn

7,2902716

answered Mar 25 at 7:40

ShawnShawn

7,2902716

answered Mar 25 at 7:40

ShawnShawn

7,2902716