How to tell if a sqlite column is AUTOINCREMENT?

Question

I’m using a sqlite database, and i wanna know if a specific column is AUTOINCREMENT or NOT

I’ve tried

PRAGMA table_info('table name') ;

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

Answer 1

Querying with

PRAGMA TABLE_INFO(yourtable);

you can get the primary key column name.

To check whether it is an autoincrement column, check whether the table has an autoincrement sequence:

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

Interpretation:

• If the count came out as non-zero, the table has an autoincrement primary key column.

• If the count came out as zero, the table is either empty and has never contained data, or does not have an autoincrement primary key column.

Although the SQLite documentation seems to imply that the sqlite_sequence table is populated when the table is created in fact this is not the case and the count only becomes available after data is inserted.

Answer 2

Forgive me, for 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:

1. The autoincrement flag is present on your column definition inside of sqlite_master
2. The column is of type integer (as currently required by SQLite)

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

Of course...

You can also simply process your sqlite_master.sql content in some client, e.g. a Java program, using simple regexes, if that's easier for you.