syntax error while compiling sqlite

Question

I'm trying to make a new table for my database with sqlite:

String CREATE_ARCHIVE_TABLE = 
"CREATE TABLE {0} ({1} INTEGER PRIMARY KEY AUTOINCREMENT," +
" {2} TEXT NOT NULL, {3} TEXT NOT NULL, {4} TEXT NOT NULL, {5} INTEGER);";

db.execSQL(MessageFormat.format(CREATE_ARCHIVE_TABLE,AItext.TABLE_NAME,AItext._ID,
        AItext.TITLE,AItext.MESSAGE,AItext.DATE,AItext.TYPE));

with the interface:

public interface AItext extends BaseColumns {
  String TABLE_NAME = "table_name";
  String TITLE = "title";
  String MESSAGE = "message";
  String DATE = "date";
  String TYPE = "type";
  String[] COLUMNS = new String[]
  { _ID, TITLE, MESSAGE, DATE, TYPE };
}

but I have the following exception and I can't see the error

android.database.sqlite.SQLiteException: near "INTEGER": syntax error: , 
while compiling: CREATE TABLE archive_contacts_name (_id INTEGER PRIMARY KEY
AUTOINCREMENT, message INTEGER, name TEXT NOT NULL, phone TEXT NOT NULL,
check INTEGER, note TEXT NOT NULL);

You have message INTEGER in your Table definition, but I'm pretty sure you want that to be a TEXT too — tolgap
– tolgap, Commented Aug 23, 2013 at 14:39
check is reserved keyword. I believe it can not be use as column name. — aim
– aim, Commented Aug 23, 2013 at 14:42

Community · Accepted Answer · 2020-06-20 09:12:55Z

1

As @aim has said, CHECK is in fact a SQLite Keyword that cannot be used as a column name.

A CHECK constraint may be attached to a column definition or specified as a table constraint. In practice it makes no difference. Each time a new row is inserted into the table or an existing row is updated, the expression associated with each CHECK constraint is evaluated and cast to a NUMERIC value in the same way as a CAST expression. If the result is zero (integer value 0 or real value 0.0), then a constraint violation has occurred. If the CHECK expression evaluates to NULL, or any other non-zero value, it is not a constraint violation. The expression of a CHECK constraint may not contain a subquery.

CHECK constraints have been supported since version 3.3.0. Prior to version 3.3.0, CHECK constraints were parsed but not enforced.

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Aug 23, 2013 at 14:49

TronicZomB

8,7477 gold badges37 silver badges50 bronze badges

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

Collectives™ on Stack Overflow

syntax error while compiling sqlite

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related