CODEWITHSUNDEEP
flutterdartsqflite
wahyu
wahyu

Reputation: 2405

How to add more column after table has been created in sqflite flutter

I have created a database that consist of one table with the name table_x. table_x has 3 columns (id, name, address). Here is the code when creating the table

Future create(Database db) async {
    await db.execute('''
          CREATE TABLE $table (
            $id INTEGER PRIMARY KEY,
            $name TEXT NOT NULL,
            $addr TEXT NOT NULL
          )
          ''');
  }

when the table has been created, I try to add one more column by adding one more line:

Future create(Database db) async {
    await db.execute('''
          CREATE TABLE $table (
            $id INTEGER PRIMARY KEY,
            $name TEXT NOT NULL,
            $addr TEXT NOT NULL,
            $phone TEXT NOT NULL
          )
          ''');
  }

but the result is throwing an error:

DatabaseException(Error Domain=FMDatabase Code=1 "table table_x has no column named phone" UserInfo={NSLocalizedDescription=table table_x has no column named phone}) sql 'INSERT INTO table_x (id,name,addr) VALUES (?, ?, ?)' args [.........]}

is there a way to modify data column after a table has been created ?

Upvotes: 1

Views: 1662

Answers (2)

Ambareesha Av
Ambareesha Av

Reputation: 21

I did something like this in my project

Future _onUpgrade(Database db, int oldVersion, int newVersion) async {
    log('old version: $oldVersion, new version: $newVersion');
    var batch = db.batch();
    if (newVersion == 2) {
      db.execute('ALTER TABLE TableName ADD newColumn TEXT');
    }
    await batch.commit();
  }

Database database = await openDatabase(
        join(path, dbName),
        version: 2,
        onCreate: _onCreate,
        onUpgrade: _onUpgrade,
      );

link to official examples https://github.com/tekartik/sqflite/blob/master/sqflite/doc/migration_example.md

Upvotes: 0

Sola Oshinowo
Sola Oshinowo

Reputation: 609

Follow this pattern to add a new column

ALTER TABLE database_name.table_name ADD COLUMN column_def...;

You are using a create table statement to create a new column in an existing table, this is wrong.

An alter table statement is what is to be used to add a new column to an existing table

Upvotes: 1

Related Questions