How to check if a Column is AUTOINCREMENT Primary key or not in Sqlite?

sqlite
sql

(SAM) #1

Is there any way to find whither a database table column is Primary Key and AutoIncrement, i am using Sqlite version #3.

If possible i prefer to get that through an SQL query, i’ve already checked table_info data table, but with no luck.


(Sine) #2

First you should understand that at SQLite AUTOINCREMENT can only be applied to PRIMARY KEY’s,

so you can follow these steps:

1.Check if the table has any primary keys, and get them from the table_info table, using the query below:

PRAGMA table_info("<table name here>");

2.You can go through the returned results using a CURSOR, and only select ones that has "pk" column value different from 0, which indicates that is a Primary Key.

3.Now you may check if the your data table, has any AUTOINCREMENT Column. can do that by checking the sqlite_master table, this way:

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

4.That’s it.