Android SQLite - ExecSQL syntax error

Question

I am completely at lost on what is the syntax error with this create table statement. It is one of the 5 tables that I am creating, 4 created successfully but this one is failing

Here is the create statement

//String to create a transaction table
private static final String CREATE_TRANSACTION_TABLE =
        "CREATE TABLE " + Constants.TRANSACTION_TABLE + "("
                + Constants.COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
                + Constants.COLUMN_CUSTOMER_ID + " INTEGER, "
                + Constants.COLUMN_DATE_CREATED + " BIGINT, "
                + Constants.COLUMN_SUB_TOTAL_AMOUNT + " NUMERIC, "
                + Constants.COLUMN_LINE_ITEMS + " TEXT, "
                + Constants.COLUMN_TAX_AMOUNT + " NUMERIC, "
                + Constants.COLUMN_TOTAL_AMOUNT + " NUMERIC, "
                + Constants.COLUMN_LAST_UPDATED + " BIGINT, "
                + "FOREIGN KEY(customer_id) REFERENCES customer(_id)" + ")";

And below is the stack trace with the error highlighted

08-24 19:13:25.376  10608-10608/com.okason.prontopos D/DatabaseHelper﹕ CREATE TABLE category(_id INTEGER PRIMARY KEY AUTOINCREMENT,name TEXT NOT NULL, image_path TEXT, create_date BIGINT, last_update_date BIGINT )
08-24 19:13:25.376  10608-10608/com.okason.prontopos D/DatabaseHelper﹕ CREATE TABLE customer(_id INTEGER PRIMARY KEY AUTOINCREMENT,name TEXT NOT NULL, email TEXT, phone TEXT, street1 TEXT, street2 TEXT, city TEXT, state TEXT, zip TEXT create_date BIGINT, last_update_date BIGINT, points NUMERIC )
08-24 19:13:25.376  10608-10608/com.okason.prontopos D/DatabaseHelper﹕ CREATE TABLE retailer(_id INTEGER PRIMARY KEY AUTOINCREMENT,name TEXT NOT NULL, email TEXT, phone TEXT, street1 TEXT, street2 TEXT, city TEXT, state TEXT, zip TEXT, industry TEXT, create_date BIGINT, last_update_date BIGINT, manager_name TEXT )
08-24 19:13:25.376  10608-10608/com.okason.prontopos D/DatabaseHelper﹕ CREATE TABLE product(_id INTEGER PRIMARY KEY AUTOINCREMENT,name TEXT NOT NULL, description TEXT, price NUMERIC, image_path TEXT, category_id INTEGER, create_date BIGINT, last_update_date BIGINT, FOREIGN KEY(category_id) REFERENCES category(_id))
08-24 19:13:25.380  10608-10608/com.okason.prontopos D/DatabaseHelper﹕ CREATE TABLE transaction(_id INTEGER PRIMARY KEY AUTOINCREMENT,customer_id INTEGER, create_date BIGINT, sub_total_amount NUMERIC, items TEXT, tax_amount NUMERIC, total_amount NUMERIC, last_update_date BIGINT, FOREIGN KEY(customer_id) REFERENCES customer(_id))


08-24 19:13:25.380  10608-10608/com.okason.prontopos E/SQLiteLog﹕ (1) near "transaction": syntax error
08-24 19:13:25.380  10608-10608/com.okason.prontopos E/DatabaseHelper﹕ near "transaction": syntax error (code 1): , while compiling: CREATE TABLE transaction(_id INTEGER PRIMARY KEY AUTOINCREMENT,customer_id INTEGER, create_date BIGINT, sub_total_amount NUMERIC, items TEXT, tax_amount NUMERIC, total_amount NUMERIC, last_update_date BIGINT, FOREIGN KEY(customer_id) REFERENCES customer(_id))null

laalto · Accepted Answer · 2015-08-24 19:57:31Z

3

transaction is a keyword in SQL. To use it as an identifier, you need to quote it e.g. in double quotes as "transaction". Or just rename your table to e.g. transactions to make it a non-keyword identifier.

answered Aug 24, 2015 at 19:57

laalto

153k68 gold badges293 silver badges306 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Phantômaxx Over a year ago

Side note: Normalization rules (and common sense) want all table names to be plurals.

Collectives™ on Stack Overflow

Android SQLite - ExecSQL syntax error

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related