How to determine if a sqlite column is AUTOINCREMENT?

Question

I am using sqlite database and I want to know if a particular column is AUTOINCREMENT or NOT

Ive tried

PRAGMA table_info('table name') ; 

But it only gives me ID, NAME, TYPE, PRIMARY KEY, NOT NULL and DEFAULT VALUE

Answer 1

Request with

 PRAGMA TABLE_INFO(yourtable); 

you can get the primary key column name.

To check if a column is an auto-increment, check if the table has a sequence of auto-increments:

 SELECT COUNT(*) FROM sqlite_sequence WHERE name='yourtable'; 

Interpretation:

• If the counter exits as non-zero, the table has a column of the primary auto-increment key.

• If the counter was zero, the table was either empty, or never contained data, or did not have a primary increment key column.

Although the SQLite documentation seems to imply that the sqlite_sequence table is populated when the table is actually created, it is not, and the account becomes available only after data entry.

Answer 2

Forgive me because I have sinned:

 WITH RECURSIVE a AS ( SELECT name, lower(replace(replace(sql, char(13), ' '), char(10), ' ')) AS sql FROM sqlite_master WHERE lower(sql) LIKE '%integer% autoincrement%' ), b AS ( SELECT name, trim(substr(sql, instr(sql, '(') + 1)) AS sql FROM a ), c AS ( SELECT b.name, sql, '' AS col FROM b UNION ALL SELECT c.name, trim(substr(c.sql, ifnull(nullif(instr(c.sql, ','), 0), instr(c.sql, ')')) + 1)) AS sql, trim(substr(c.sql, 1, ifnull(nullif(instr(c.sql, ','), 0), instr(c.sql, ')')) - 1)) AS col FROM c JOIN b ON c.name = b.name WHERE c.sql != '' ), d AS ( SELECT name, substr(col, 1, instr(col, ' ') - 1) AS col FROM c WHERE col LIKE '%autoincrement%' ) SELECT name, col FROM d ORDER BY name, col; 

This query is based on two assumptions:

• The autoincrement flag autoincrement present in the column definition inside sqlite_master
• The column is of type integer (as SQLite currently requires)

Because regular expressions are not available out of the box, this query uses a recursive approach to match all columns. Please forgive me.

Sure...

You can also just process your sqlite_master.sql content with some client, for example. Java program using simple regular expressions, if that’s easier for you.

Answer 3

The primary key is the only field in SQLite that can auto-increment using the SQLite auto-increment keyword. If you want another auto-increment field, you have to hack it yourself .

 .schema table_name 

This will show the CREATE statement used to create the table in question, including subsequent column additions using ALTER TABLE table_name ADD COLUMN column_name . If you declare a primary key, it will appear here.

The primary key will not necessarily explicitly indicate AUTOINCREMENT in the .schema report, but does not fall into omission. It is clearly consistent with the bill .

Note that I click the PRAGMA writable_schema=ON; trick PRAGMA writable_schema=ON; . Even if you insert columns using this PRAGMA , the .schema window appears.