Android SQLite table creation failure

Question

I am writing tests for my database and right now the table below is not building and the test is failing with this SQL command being built: " CREATE TABLE location (_id INTEGER PRIMARY KEY,location_setting TEXT UNIQUE NOT NULL, city_name TEXT NOT NULL, coord_lat REAL NOT NULL coord_long REAL NOT NULL ); ". I am pretty certain there is some syntax error yet I am new to databases in android and honestly need help.

Table creation:

@Override
    public void onCreate(SQLiteDatabase sqLiteDatabase) {
        final String SQL_CREATE_LOCATION_TABLE = "CREATE TABLE " + LocationEntry.TABLE_NAME + " (" +
                LocationEntry._ID + " INTEGER PRIMARY KEY," +
                LocationEntry.COLUMN_LOCATION_SETTING + " TEXT UNIQUE NOT NULL, " +
                LocationEntry.COLUMN_CITY_NAME + " TEXT NOT NULL, " +
                LocationEntry.COLUMN_COORD_LAT + " REAL NOT NULL " +
                LocationEntry.COLUMN_COORD_LONG + " REAL NOT NULL " +
                " );";
    }

Thanks for the help.

Umer Asif · Answer 1 · 2016-01-24T21:08:12.153

3

You need to add ',' after every column like this just leave the last.

    final String SQL_CREATE_LOCATION_TABLE = "CREATE TABLE " + LocationEntry.TABLE_NAME + " (" +
            LocationEntry._ID + " INTEGER PRIMARY KEY," +
            LocationEntry.COLUMN_LOCATION_SETTING + " TEXT UNIQUE NOT NULL, " +
            LocationEntry.COLUMN_CITY_NAME + " TEXT NOT NULL, " +
            LocationEntry.COLUMN_COORD_LAT + " REAL NOT NULL, " +
            LocationEntry.COLUMN_COORD_LONG + " REAL NOT NULL" +
            " );";

edited Jan 24 '16 at 21:08

answered Jan 24 '16 at 20:56

Umer Asif

441
1
4
15

1

yup OP missed a comma before last field :) good catch – Yazan Jan 24 '16 at 22:15

score -1 · Answer 2 · edited May 23 '17 at 12:23

-1

You can not use TEXT with UNIQUE.

For more info: make text column as unique key.

You can try VARCHAR or think of a different implementation to reach your goal.

Bye

edited May 23 '17 at 12:23

Community

1
1

answered Jan 24 '16 at 20:55

Amedeo Baragiola

314
4
14

He's asking for SQLite not MySQL. In SQLite you can create TEXT + UNIQUE column definitions. – Mateusz Herych Jan 24 '16 at 21:16

Android SQLite table creation failure

2 Answers2