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;
Related videos on Youtube
Comments
-
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 itThanks
-
GamerJosh almost 11 years
-
-
Muhammad Adil almost 9 yearsNice 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 about 8 yearsThere is a detailed information about this method in the Sqlite documentation : sqlite.org/lang_altertable.html "Making Other Kinds Of Table Schema Changes"
-
mxmlnkn over 4 yearsOn 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).