Re: [sqlite] How many AUTOINCREMENT tables are in your schema?

[David Raymond]

select name
from sqlite_master
where
type = 'table'
and exists (
  select 1
  from pragma_table_info(sqlite_master.name)
  where pk > 0
)
and not exists (
  select 1
  from pragma_index_list(sqlite_master.name)
  where origin = 'pk'
)
order by name;

Tables which have a primary key, but no index created by a primary key clause.
I <think> that covers only integer primary key tables, but am not sure for 
things like extensions, virtual tables, etc.


SQLite version 3.22.0 2018-01-22 18:45:57
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.

sqlite> .timer off

sqlite> .eqp off

sqlite> .mode column

sqlite> create table ipk (id integer primary key, foo, bar);

sqlite> create table intpk (id int primary key, foo, bar);

sqlite> create table nopk (id integer, foo, bar);

sqlite> create table cpk (id1 integer, id2 integer, foo, bar, primary key (id1, 
id2));

sqlite> create table wri (id integer primary key, foo, bar) without rowid;

sqlite> create table ipkd (id integer primary key desc, foo, bar);

sqlite> select name
   ...> from sqlite_master
   ...> where
   ...> type = 'table'
   ...> and exists (
   ...>   select 1 from pragma_table_info(sqlite_master.name)
   ...>   where pk > 0
   ...> )
   ...> and not exists (
   ...>   select 1
   ...>   from pragma_index_list(sqlite_master.name)
   ...>   where origin = 'pk'
   ...> )
   ...> order by name;
name
----------
ipk

sqlite>


-----Original Message-----
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Peter Halasz
Sent: Tuesday, March 20, 2018 2:50 AM
To: SQLite mailing list
Subject: Re: [sqlite] How many AUTOINCREMENT tables are in your schema?

When needed I use a declared INTEGER PRIMARY KEY.
>
>
MAYBE THAT WOULD HAVE BEEN IN THE SURVEY TOO BUT I GUESS THERE WAS NO WAY
TO INCLUDE A SMALL PIECE OF SQL TO RELIABLY CHECK FOR INTEGER PRIMARY KEY

YES I AM SHOUTING
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users