How to change primary key of table in SQLDelight?

Question

We are working on some project where we have SQLDelight database and DBRevisionPhrase table:

CREATE TABLE DbRevisionPhrase (
    userId TEXT NOT NULL,
    phraseId TEXT NOT NULL,
    vocabularyId TEXT NOT NULL, 
    isMissed INTEGER AS Boolean NOT NULL DEFAULT 0,
    countAnswers INTEGER NOT NULL,
    PRIMARY KEY (userId, phraseId, vocabularyId)
);

After we wanted to add a new field reviewType, and add it to PRIMARY KEY, it turned out like that:

import com.baoui.db.ReviewType;

CREATE TABLE DbRevisionPhrase (
    userId TEXT NOT NULL,
    phraseId TEXT NOT NULL,
    vocabularyId TEXT NOT NULL, 
    isMissed INTEGER AS Boolean NOT NULL DEFAULT 0,
    countAnswers INTEGER NOT NULL,
    type TEXT AS ReviewType,
    PRIMARY KEY (userId, phraseId, vocabularyId, type)
);

But we also need a migration, trying this we are failed:

ALTER TABLE DbRevisionPhrase ADD COLUMN type TEXT;
ALTER TABLE DbRevisionPhrase DELETE PRIMARY KEY;
ALTER TABLE DbRevisionPhrase ADD PRIMARY KEY (userId, phraseId, vocabularyId, type);

with error:

android.database.sqlite.SQLiteException: incomplete input (code 1 SQLITE_ERROR): , while compiling: ALTER TABLE DbRevisionPhrase

Do we can somehow change the primary key of table, or we need to remove table, and create new one with moving all data?

score 3 · Answer 1 · answered May 21 '20 at 11:36

This won't be possible with an ALTER TABLE statement unfortunately. You will have to create a new table and migrate the existing table over. It will look something like this:

CREATE TABLE DbRevisionPhrase_new(
  ...
);
INSERT INTO DbRevisionPhrase_new 
SELECT * FROM DbRevisionPhrase;
DROP TABLE DbRevisionPhrase;
ALTER TABLE DbRevisionPhrase_new RENAME TO DbRevisionPhrase;

How to change primary key of table in SQLDelight?

1 Answers1