CODEWITHSUNDEEP
javasqlitejdbc
rmaik
rmaik

Reputation: 1086

how to make non primary key column is auto incremented

i am using sqlite with java JDBC, and i created the following table:

private final String sqlTableNode = "CREATE TABLE "+this.TABLE_NODE+
        " ( "+this.NODE_TABLE_ID_COL+" INTEGER, " +
        this.NODE_TABLE_NODE_ID_COL+" TEXT NOT NULL, " +
        this.NODE_TABLE_LAT_COL+" TEXT NOT NULL, " +
        this.NODE_TABLE_LNG_COL+" TEXT NOT NULL, " +
        "PRIMARY KEY ("+this.NODE_TABLE_LAT_COL+", "+this.NODE_TABLE_LNG_COL+") );";

my question is, how can i make the first column "this.NODE_TABLE_PK_COL" is auto incremented while it is not a primary key??

Upvotes: 1

Views: 593

Answers (3)

Maverick
Maverick

Reputation: 1599

@rmaik: No. You can't. You can find the documentation here : https://www.sqlite.org/autoinc.html

It clearly states :

AUTOINCREMENT is not allowed on WITHOUT ROWID tables or on any table column other than INTEGER PRIMARY KEY. Any attempt to use AUTOINCREMENT on a WITHOUT ROWID table or on a column other than the INTEGER PRIMARY KEY column results in an error.

If I'm understanding your requirement, what you can do in this scenario is that, create primary key on

this.NODE_TABLE_ID_COL.

Since, it is integer, it fits the criteria for Primary key.

And create unique key on :

UNIQUE KEY ("+this.NODE_TABLE_ID_COL+", "+this.NODE_TABLE_LAT_COL+", "+this.NODE_TABLE_LNG_COL+") )

Upvotes: 1

mthmulders
mthmulders

Reputation: 9705

Unfortunately, it's not possible. The SQLite documentation on Autoincrement says at the bottom of the page:

Because AUTOINCREMENT keyword changes the behavior of the ROWID selection algorithm, AUTOINCREMENT is not allowed on WITHOUT ROWID tables or on any table column other than INTEGER PRIMARY KEY. Any attempt to use AUTOINCREMENT on a WITHOUT ROWID table or on a column other than the INTEGER PRIMARY KEY column results in an error.

Upvotes: 5

CL.
CL.

Reputation: 180270

As the documentation shows, autoincrement works only for the primary key.

Upvotes: 4

Related Questions