Database Exception Flutter sqflite

Question

i want to create multiple table like this

static Future<void> createTables(sql.Database database) async {
    await database.execute("""CREATE TABLE cart (
      id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
      food TEXT,
      price INTEGER,
      qty INTEGER,
      cashier TEXT,
      createdAt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
    ) """);
    await database.execute("""CREATE TABLE transaction (
      id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
      no_nota TEXT,
      food TEXT,
      price INTEGER,
      qty INTEGER,
      cashier TEXT,
      createdAt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
    ) """);
}

But after i build/run application, the application throws error

Exception has occurred. SqfliteDatabaseException (DatabaseException(near "transaction": syntax error (code 1 SQLITE_ERROR): , while compiling: CREATE TABLE transaction ( id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, no_nota TEXT, food TEXT, price INTEGER, qty INTEGER, cashier TEXT, createdAt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP )) sql 'CREATE TABLE transaction ( id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, no_nota TEXT, food TEXT, price INTEGER, qty INTEGER, cashier TEXT, createdAt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ) ' args [])

how should i do ? Thanks

Answer 1

Avoid reserved keywords in SQL or escape them

The issue is with the table name you're trying to create. The word "transaction" is a reserved keyword in SQLite, which means you can't use it directly as a table name without escaping it properly.

It is either you change the table name or escape it like so `transaction`.. Consider the below code

    CREATE TABLE `transaction` (
  id INTEGER PRIMARY KEY 
  AUTOINCREMENT NOT NULL,
  no_nota TEXT,
  food TEXT,
  price INTEGER,
  qty INTEGER,
  cashier TEXT,
  createdAt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
)