SQLite add Primary Key

Question

I created a table in Sqlite by using the CREATE TABLE AS syntax to create a table based on a SELECT statement   Now this table has no primary key but I would like to add one   Executing ALTER TABLE table name ADD PRIMARY KEY col1  col2      gives a syntax error  near PRIMARY   Is there a way to add a primary key either during table creation or afterwards in Sqlite   By  during creation  I mean during creation with CREATE TABLE AS

User · Answer

Introduction  This is based on Android s java and it s a good example on changing the database without annoying your application fans customers  This is based on the idea of the SQLite FAQ page  http   sqlite org faq html q11  The problem   I did not notice that I need to set a row number or record id to delete a single purchased item in a receipt  and at same time the item barcode number fooled me into thinking of making it as the key to delete that item  I am saving a receipt details in the table receipt barcode  Leaving it without a record id can mean deleting all records of the same item in a receipt if I used the item barcode as the key   Notice  Please understand that this is a copy-paste of my code I am work on at the time of this writing  Use it only as an example  copy-pasting randomly won t help you  Modify this first to your needs  Also please don t forget to read the comments in the code    The Code  Use this as a method in your class to check 1st whether the column you want to add is missing   We do this just to not repeat the process of altering the table receipt barcode   Just mention it as part of your class  In the next step you ll see how we ll use it   public boolean is column exists SQLiteDatabase mDatabase   String table name  String     column name          checks if table name has column name     Cursor cursor   mDatabase rawQuery  pragma table info   table name     null       while  cursor moveToNext         if  cursor getString cursor getColumnIndex  name    equalsIgnoreCase column name   return true            return false      Then   the following code is used to create the table receipt barcode if it already does NOT exit for the 1st time users of your app  And please notice the  IF NOT EXISTS  in the code  It has importance     mDatabase should be defined as a Class member  global variable     for ease of access      SQLiteDatabse mDatabase SQLiteDatabase openOrCreateDatabase dbfile path  null   creation query     CREATE TABLE if not exists receipt barcode      creation query      n record id        INTEGER PRIMARY KEY AUTOINCREMENT    creation query      n rcpt id INT  11         NOT NULL    creation query      n barcode VARCHAR  255    NOT NULL     creation query      n barcode price VARCHAR  255    DEFAULT  0     creation query      n PRIMARY KEY   record id        mDatabase execSQL creation query      This is where the important part comes in regarding the question in this page     adding the missing primary key record id in table receipt barcode for older versions         if   is column exists mDatabase   receipt barcode   record id                 mDatabase beginTransaction                try                  Log e  record id    creating                       creation query  CREATE TEMPORARY TABLE t1 backup                     creation query   record id INTEGER        PRIMARY KEY AUTOINCREMENT                     creation query   rcpt id INT  11         NOT NULL                     creation query   barcode VARCHAR  255    NOT NULL                      creation query   barcode price VARCHAR  255    NOT NULL DEFAULT  0                        mDatabase execSQL creation query                     creation query  INSERT INTO t1 backup rcpt id barcode barcode price  SELECT rcpt id barcode barcode price  FROM receipt barcode                     mDatabase execSQL creation query                     creation query  DROP TABLE receipt barcode                     mDatabase execSQL creation query                     creation query  CREATE TABLE receipt barcode                      creation query   record id INTEGER        PRIMARY KEY AUTOINCREMENT                     creation query   rcpt id INT  11         NOT NULL                     creation query   barcode VARCHAR  255    NOT NULL                      creation query   barcode price VARCHAR  255    NOT NULL DEFAULT  0                        mDatabase execSQL creation query                     creation query  INSERT INTO receipt barcode record id rcpt id barcode barcode price  SELECT record id rcpt id barcode barcode price  FROM t1 backup                     mDatabase execSQL creation query                     creation query  DROP TABLE t1 backup                     mDatabase execSQL creation query                      mdb setTransactionSuccessful                  catch  Exception exception                    Log e  table receipt bracode    Table receipt barcode did not get a primary key  record id                    exception printStackTrace                  finally                    mDatabase endTransaction

User · Answer

As long as you are using CREATE TABLE  if you are creating the primary key on a single field  you can use   CREATE TABLE mytable   field1 TEXT  field2 INTEGER PRIMARY KEY  field3 BLOB       With CREATE TABLE  you can also always use the following approach to create a primary key on one or multiple fields   CREATE TABLE mytable   field1 TEXT  field2 INTEGER  field3 BLOB  PRIMARY KEY  field2  field1       Reference  http   www sqlite org lang createtable html  This answer does not address table alteration

User · Answer

sqlite gt   create table t id integer  col2 varchar 32   col3 varchar 8    sqlite gt   insert into t values 1   he    ha    sqlite gt  sqlite gt   create table t2 id integer primary key  col2 varchar 32   col3 varchar 8    sqlite gt   insert into t2 select   from t  sqlite gt   schema CREATE TABLE t id integer  col2 varchar 32   col3 varchar 8    CREATE TABLE t2 id integer primary key  col2 varchar 32   col3 varchar 8    sqlite gt  drop table t  sqlite gt  alter table t2 rename to t  sqlite gt   schema CREATE TABLE IF NOT EXISTS  quot t quot  id integer primary key  col2 varchar 32   col3 varchar 8

User · Answer

According to the sqlite docs about table creation  using the create table as select produces a new table without constraints and without primary key   However  the documentation also says that primary keys and unique indexes are logically equivalent  see constraints section       In most cases  UNIQUE and PRIMARY KEY constraints are implemented by creating a unique index in the database   The exceptions are INTEGER PRIMARY KEY and PRIMARY KEYs on WITHOUT ROWID tables   Hence  the following schemas are logically equivalent   CREATE TABLE t1 a  b UNIQUE    CREATE TABLE t1 a  b PRIMARY KEY    CREATE TABLE t1 a  b   CREATE UNIQUE INDEX t1b ON t1 b       So  even if you cannot alter your table definition through SQL alter syntax  you can get the same primary key effect through the use an unique index   Also  any table  except those created without the rowid syntax  have an inner integer column known as  rowid   According to the docs  you can use this inner column to retrieve modify record tables

User · Answer

You can t modify SQLite tables in any significant way after they have been created  The accepted suggested solution is to create a new table with the correct requirements and copy your data into it  then drop the old table   here is the official documentation about this  http   sqlite org faq html q11

User · Answer

I had the same problem and the best solution I found is to first create the table defining primary key and then to use insert into statement   CREATE TABLE mytable   field1 INTEGER PRIMARY KEY  field2 TEXT     INSERT INTO mytable  SELECT field1  field2  FROM anothertable

User · Answer

You can do it like this   CREATE TABLE mytable   field1 text  field2 text  field3 integer  PRIMARY KEY  field1  field2

User · Answer

I used the CREATE TABLE AS syntax to merge several columns and encountered the same problem  Here is an AppleScript I wrote to speed the process up    set databasePath to    Documents Databases example db  set tableOne to  separate  -- Table from which you are pulling data set tableTwo to  merged  -- Table you are creating set  tempCol  tempColEntry  permColEntry  to              set permCol to   id integer primary key    -- Columns are created from single items  AND from the last item of a list --    a    b    c     d    e   Columns  a  and  b  will be merged into a new column  c    tableTwo will have columns  c    d    e   set nonCoal to   City    Contact    Names     Address 1    Address    address one    Address1    Text4    Address 1      E-Mail    E-Mail Address    Email    Email Address    EmailAddress    Email      Zip    Zip Code    ZipCode    Zip      Telephone    BusinessPhone    Phone    Work Phone    Telephone      St    State    State      Salutation    Mr Ms    Mr s    Salutations    Sautation    Salutation     -- Build the COALESCE statements repeat with h from 1 to count of nonCoal set aColumn to item h of nonCoal if class of aColumn is not list then     if  count of words of aColumn   gt  1 then set aColumn to quote  amp  aColumn  amp  quote     set end of tempCol to aColumn     set end of permCol to aColumn else     set coalEntry to        repeat with i from 1 to count of aColumn         set coalCol to item i of aColumn as string         if  count of words of coalCol   gt  1 then set coalCol to quote  amp  coalCol  amp  quote         if i   1 then             set end of coalEntry to  TRIM COALESCE    amp  coalCol  amp                               else if i  lt    count of aColumn  - 1  then             set end of coalEntry to  COALESCE    amp  coalCol  amp                               else if i     count of aColumn  - 1  then             set as Col to item  i   1  of aColumn as string             if  count of words of as Col   gt  1 then set as Col to quote  amp  as Col  amp  quote             set end of coalEntry to   COALESCE    amp  coalCol  amp          AS    amp  as Col   amp                 set end of permCol to as Col         end if     end repeat     set end of tempCol to  coalEntry as string  end if end repeat  -- Since there are       within the COALESCE statement  you can t use  TID  and  as string  to convert tempCol and permCol for entry into sqlite3  I rebuild the lists in the next block  repeat with j from 1 to count of tempCol if j  lt   count of tempCol  then     set end of tempColEntry to item j of tempCol  amp           set end of permColEntry to item j of permCol  amp       else     set end of tempColEntry to item j of tempCol     set end of permColEntry to item j of permCol end if end repeat set end of permColEntry to       amp  item  j   1  of permCol set permColEntry to  permColEntry as string  set tempColEntry to  tempColEntry as string   -- Create the new table with an  id integer primary key  column set createTable to  create table    amp  tableTwo  amp        amp  permColEntry  amp        do shell script  sqlite3    amp  databasePath  amp  space  amp  quoted form of createTable  -- Create a temporary table and then populate the permanent table set createTemp to  create temp table placeholder as select    amp  tempColEntry  amp    from    amp  tableOne  amp         amp   insert into    amp  tableTwo  amp    select Null    from placeholder   do shell script  sqlite3    amp  databasePath  amp  space  amp  quoted form of createTemp  --export the new table as a  csv file do shell script  sqlite3 -header -column -csv    amp  databasePath  amp      select   from    amp  tableTwo  amp         gt       amp  tableTwo  amp    csv

User · Answer

I tried to add the primary key afterwards by changing the sqlite master table directly  This trick seems to work  It is a hack solution of course    In short  create a regular  unique  index on the table  then make the schema writable and change the name of the index to the form reserved by sqlite to identify a primary key index   i e  sqlite autoindex XXX 1  where XXX is the table name  and set the sql string to NULL  At last change the table definition itself  One pittfal  sqlite does not see the index name change until the database is reopened  This seems like a bug  but not a severe one  even without reopening the database  you can still use it     Suppose the table looks like   CREATE TABLE tab1 i INTEGER  j INTEGER  t TEXT     Then I did the following   BEGIN  CREATE INDEX pk tab1 ON tab1 i j   pragma writable schema 1  UPDATE sqlite master SET name  sqlite autoindex tab1 1  sql null WHERE name  pk tab1   UPDATE sqlite master SET sql  CREATE TABLE tab1 i integer j integer t text primary key i j    WHERE name  tab1   COMMIT    Some tests  in sqlite shell    sqlite gt  explain query plan select   from tab1 order by i j  0 0 0 SCAN TABLE tab1 USING INDEX sqlite autoindex tab1 1 sqlite gt  drop index sqlite autoindex tab1 1  Error  index associated with UNIQUE or PRIMARY KEY constraint cannot be dropped

[sqlite] SQLite add Primary Key

Examples related to sqlite