CODEWITHSUNDEEP
sqliteauto-increment
yaylitzis
yaylitzis

Reputation: 5534

Autoincrement in SQLite tables

I create a table, lets name it CUSTOMERS in SQLite:

CREATE TABLE "CUSTOMERS" (
    "tel" INTEGER NOT NULL, 
    "customer" VARCHAR ,
);

When I see the table from a GUI (I use SQLite Manager from Firefox), I noticed that there is an extra column rowid which is working like auto-increment. My question is, in tables where I don't use a primary key should I specify a column like: 

ROWID INTEGER PRIMARY KEY AUTOINCREMENT

If I execute this query PRAGMA table_info(CUSTOMERS); I get only the two columns tel,customer.

Upvotes: 1

Views: 205

Answers (2)

yaylitzis
yaylitzis

Reputation: 5534

You can see here, as it was commented in your question.

However, if you are in a dilemma what to choose between these options, SQLite recommends that you should not use AUTOINCREMENT attribute because:

The AUTOINCREMENT keyword imposes extra CPU, memory, disk space, and disk I/O overhead and should be avoided if not strictly needed. It is usually not needed.

More info you can read here.

Upvotes: 0

mksteve
mksteve

Reputation: 13073

Sqlite usually adds a rowid automatically as @laato linked in the comments SqLite : ROWID

That can be removed, but does not need to be specified. So there is no need to add it to the Create Table.

The hidden rowid allows delete's to be targetted at a single row, bu t if you are using the ROWID as a specific foreign key, it would be better to name a column explicitly. That will then become a synonym with the rowid.

Upvotes: 1

Related Questions