CODEWITHSUNDEEP
sqlsqlite
Abde'llah Gym
Abde'llah Gym

Reputation: 121

How to tell if a sqlite column is AUTOINCREMENT?

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

Upvotes: 8

Views: 4175

Answers (3)

Ajay Tom George
Ajay Tom George

Reputation: 2279

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.

Upvotes: 1

Lukas Eder
Lukas Eder

Reputation: 220762

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.

Upvotes: 8

laalto
laalto

Reputation: 152777

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.

Upvotes: 8

Related Questions