[SOLVED] FMDB MODIFY query not working to set constraints in existing SQLite database

FMDB MODIFY query not working to set constraints in existing SQLite database

My sqlite database has existing column named order_id in tbl_order with duplicate entries. I am trying to remove duplicate entries and add constraint(UNIQUE) to solve this.

So I have tried following code to solve above issue. I have checked previous and current build version to manage existing and new database.

It successfully executes first query but showing error in modify constraint.

    if (previousBuildVersion < currentBuildVersion) {
        FMResultSet *result = nil;
        result = [self.objDB executeQuery:@"SELECT DISTINCT order_id FROM tbl_order ORDER BY order_id"];
        [self.objDB executeUpdate:@"ALTER TABLE 'tbl_order' MODIFY \ 'order_id' TEXT NOT NULL UNIQUE \ ;"];
    }

I am getting this error in Xcode:

[logging] near "MODIFY": syntax error

DB Error: 1 "near "MODIFY": syntax error"

Please suggest a solution to this issue as I have very less experience working with FMDB library.

Thanks in advance.

Solution

You cannot alter column in sqlite once it has been created.

Only the RENAME TABLE and ADD COLUMN variants of the ALTER TABLE command are supported. Other kinds of ALTER TABLE operations such as DROP COLUMN, ALTER COLUMN, ADD CONSTRAINT, and so forth are omitted.

Please check this link for more details : https://www.sqlite.org/omitted.html