Syntax error on creating android SQLite table in database

Question

I have created a table according to the following needs:

1. Later in my code I use cursors so I have create a column _ID in the table

2. As I only want to use my own primary key I set my column 'barcode' as the only primary key.

So the table looks like below:

CREATE TABLE product (
_id INTEGER,
barcode TEXT PRIMARY KEY NOT NULL,
title TEXT NOT NULL,
categoryid INTEGER NOT NULL,
FOREIGN KEY (categoryid) REFERENCES category (_id) ON DELETE CASCADE)

Above table is created correctly but I need _id column to be autoincremented so I modify table as below:

CREATE TABLE product (
_id INTEGER AUTOINCREMENT NOT NULL,
barcode TEXT PRIMARY KEY NOT NULL,
title TEXT NOT NULL,
categoryid INTEGER NOT NULL,
FOREIGN KEY (categoryid) REFERENCES category (_id) ON DELETE CASCADE)

When debugging my app, I get below error:

near "AUTOINCREMENT": syntax error (code 1): , while compiling: 
CREATE TABLE product (_id INTEGER AUTOINCREMENT NOT NULL,barcode TEXT PRIMARY KEY NOT NULL,title TEXT NOT NULL,categoryid INTEGER NOT NULL, FOREIGN KEY (categoryid) REFERENCES category (_id) ON DELETE CASCADE)

I need barcode column to be the only primary key but _ID column to be autoincrement at the same time.

I could use a composite primary key consisting on _ID and barcode, so then _ID can be set to autoincrement without problems, but doing so more than 1 product could be inserted with the same barcode (having different _ID) and I want barcode to be unique in the entire table.

so how to get rid of this?

Answer 1

Can you not use ROWID in this situation? It does everything you are asking and is built into most sqlite tables. In your queries just do select X, Y, ROWID from Z.

Answer 2

from the link: https://www.sqlite.org/autoinc.html, auto increment cannot be set to non primary field, so I think you can try the follow solution, it might help: SQLite auto-increment non-primary key field