Change the primary key of a table in SQLite

18,566

As per the comment by @GarnerJosh, you can't run a single command to change it - SQLite doesn't permit changing existing table structures. Instead, create a new table with a composite primary key and copy the data over:

CREATE TABLE my_table_copy( 
    id INTEGER, 
    lang INTEGER,
    data TEXT,
    PRIMARY KEY (id, lang)
);
INSERT INTO my_table_copy (id, lang, data)
   SELECT id, lang, data FROM my_table;
DROP TABLE my_table;
ALTER TABLE my_table_copy RENAME TO my_table;
Share:
18,566

Related videos on Youtube

Addev
Author by

Addev

=)

Updated on June 04, 2022

Comments

  • Addev
    Addev almost 2 years

    I have the following table:

    [id,lang,data]
    

    Where the primary key is id

    I want to change the primary key to id,lang without losing the content of the table. What is the query I must run to change it

    Thanks

  • Muhammad Adil
    Muhammad Adil almost 9 years
    Nice answer, in this case make sure u are not calling onCreate(db) again from your onUpgrade(), If you are doing so then application will again try to create your table and it will cause sqliteException, table already exit. so for avoiding this only drop and create those tables which you want to change. Thank you PinnyM and GamerJosh
  • Matthieu
    Matthieu about 8 years
    There is a detailed information about this method in the Sqlite documentation : sqlite.org/lang_altertable.html "Making Other Kinds Of Table Schema Changes"
  • mxmlnkn
    mxmlnkn over 4 years
    On my testsystem using INSERT INTO my_table_copy (id, lang, data) SELECT id, lang, data FROM my_table ORDER BY (id,lang); speeds up the query by factor 2 for larger tables (~100k rows).