syntax error (code 1): , while compiling: CREATE TABLE

Question

What am I missing in my sql command?

public class PhoneDal extends SQLiteOpenHelper {

    // Database Version
    private static final int DATABASE_VERSION = 1;
    // Database Name
    private static final String DATABASE_NAME = Constants.DB_NAME;

    public static final String BLOCKED_PHONES_TABLE = "BLOCKED_PHONES_TABLE";

    public PhoneDal(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        String CREATE_BLOCKED_PHONES_TABLE =
                "CREATE TABLE "+ BLOCKED_PHONES_TABLE +
                        " ( "+ KEY_ID+" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL DEFAULT 1, "
                        + KEY_PHONE+" TEXT, "
                        + KEY_IS_BLOCKED+" BIT," +
                        " UNIQUE "+ KEY_PHONE+" )";

        db.execSQL(CREATE_BLOCKED_PHONES_TABLE);
    }

and the error is

 android.database.sqlite.SQLiteException: near "KEY_PHONE": syntax error (code 1): , while compiling: CREATE TABLE BLOCKED_PHONES_TABLE ( id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL DEFAULT 1, KEY_PHONE TEXT, KEY_IS_BLOCKED BIT, UNIQUE KEY_PHONE )
            at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)

Use the debugger to inspect the value of the String CREATE_BLOCKED_PHONES_TABLE (you really should learn the Java naming conventions) and paste it's value into your question. — Simon
– Simon, Commented Dec 31, 2014 at 13:42

laalto · Accepted Answer · 2014-12-31 13:51:58Z

3

UNIQUE columns must be defined in parens. Change

" UNIQUE "+ KEY_PHONE+" )";

to

" UNIQUE ("+ KEY_PHONE+") )";

answered Dec 31, 2014 at 13:51

laalto

153k68 gold badges293 silver badges306 bronze badges

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

2 Comments

Elad Benda2 Over a year ago

but @Mousa solved my problem differently and it worked. how come it's different than yours?

laalto Over a year ago

This way defines a table constraint and the UNIQUE can apply to more than one column. Mousa's way defines a column constraint. I answered like this since your question was using a table constraint incorrectly. sqlite.org/lang_createtable.html

Mousa Jafari · Accepted Answer · 2014-12-31 13:56:40Z

2

You have a syntax error for defining unique field
Use this KEY_PHONE + " TEXT UNIQUE" instead

Column definition in sqlite:

edited Dec 31, 2014 at 13:56

answered Dec 31, 2014 at 13:44

Mousa Jafari

6771 gold badge6 silver badges21 bronze badges

1 Comment

Simon Over a year ago

Or even better, use spaces properly - KEY_PHONE + " TEXT UNIQUE"

Collectives™ on Stack Overflow

syntax error (code 1): , while compiling: CREATE TABLE

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related