8

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

Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509
Abde'llah Gym
  • 121
  • 1
  • 4

3 Answers3

8

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.

Turnkey
  • 9,266
  • 3
  • 27
  • 36
laalto
  • 150,114
  • 66
  • 286
  • 303
  • Shouldn't there always be an entry in the sqlite_sequence if a table has an autoincrement primary key column? –  May 21 '14 at 05:50
  • 5
    This is convenient but you should be aware that it isn't bullet-proof. Consider the situation where the table was created, the column is autoincrement and primary key -- but the table does not yet have any records. In this case, relying on COUNT to determine whether the column is autoincrement will lead one astray... – dat Aug 07 '15 at 22:04
  • @Lara is correct in that if the primary key in the CREATE statement contains the keywords PRIMARY KEY AUTOINCREMENT then there will always be an entry in the sqlite_sequence table so for that case you can expect it to be deterministic if the count(*) is non-zero. http://sqlite.org/autoinc.html – Turnkey Jan 03 '17 at 22:10
  • Apologies to @laalto for my edit, his original statement was correct and have reverted the interpretation. – Turnkey Feb 05 '17 at 21:13
  • So sad, there is actually no way to tell.. for an empty table. – Erik Aronesty Apr 20 '18 at 15:01
8

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.

Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509
1

There is a thread from sam and sine in this post See Thread

SQLite AUTOINCREMENT can only be applied to PRIMARY KEY’s

Based on this logic you can use

SELECT "is-autoincrement" FROM sqlite_master WHERE tbl_name="<your-table-name>" AND sql LIKE "%AUTOINCREMENT%"

as mentioned in the thread. It worked for me. Not sure of its reliability though.

Ajay Tom George
  • 1,890
  • 1
  • 14
  • 26