Syntax error in SQLite creating a table

Question

I try to create a SQLite database. here my code below

public class DatabaseHelper extends SQLiteOpenHelper {

// Database Version
private static final int DATABASE_VERSION = 1;

// Database Name
private static final String DATABASE_NAME = "AluShareDatabaseManager";

// Table Names
public static final String TABLE_BROADCASTLIST = "broadcastlist";
public static final String TABLE_BROADCAST_MEMBERLIST = "broadcast_memberlist";
public static final String TABLE_SETTINGS = "settings";
public static final String TABLE_CHAT = "chat";
public static final String TABLE_MESSAGELIST = "messagelist";
public static final String TABLE_RECEIVERLIST = "receiverlist";
public static final String TABLE_GROUP = "group";
public static final String TABLE_GROUP_MEMBERLIST = "group_memberlist";
public static final String TABLE_CONTACTLIST = "contactlist";

public static final String ID = "id";
// TABLE_BROADCASTLIST
public static final String BROADCASTLIST_ID = "id";
public static final String BROADCASTLIST_NAME = "name";
public static final String BROADCASTLIST_NOTE = "note";

// TABLE_BROADCAST_MEMBERLIST
public static final String BROADCAST_MEMBERLIST_ID = "broadcast_memberlist_id";
public static final String BROADCAST_MEMBER_ID = "broadcast_member_id";

// TABLE_SETTINGS
public static final String SETTINGS_ID = "id";
public static final String SETTINGS_LANGUAGE = "language";
public static final String SETTINGS_FONT = "font";
public static final String SETTINGS_FONTSIZE = "fontsize";
public static final String SETTINGS_BACKGROUND = "background";

// TABLE_CHAT
public static final String CHAT_ID = "id";
public static final String CHAT_NAME = "name";
public static final String CHAT_MUTED = "muted";
public static final String CHAT_TYPE = "type";

// TABLE_MESSAGELIST
public static final String MESSAGELIST_ID = "id";
public static final String MESSAGELIST_CHAT_ID = "messagelist_chat_id";
public static final String MESSAGE_TYPE = "type";
public static final String MESSAGE_CONTENT = "content";
public static final String MESSAGE_READ_FLAG = "read_flag";
public static final String MESSAGE_DATE = "date";
public static final String MESSAGE_RECEIVERLIST_ID = "receiverlist_id";

// TABLE_RECEIVERLIST
public static final String RECEIVERLIST_ID = "receiverlist_id";
public static final String RECEIVER_ID = "receiver_id";

// TABLE_GROUP
public static final String GROUP_ID = "id";
public static final String GROUP_CHAT_ID = "group_chat_id";
public static final String GROUP_NAME = "name";
public static final String GROUP_NOTE = "note";
public static final String GROUP_ADMIN = "admin";

// TABLE_GROUP_MEMBERLIST
public static final String GROUP_MEMBERLIST_ID = "group_memberlist_id";
public static final String GROUP_MEMBER_ID = "group_member_id";

// TABLE_CONTACTLIST
public static final String CONTACT_ID = "id";
public static final String CONTACT_CHAT_ID = "contact_chat_id";
public static final String CONTACT_NAME = "name";
public static final String CONTACT_NOTE = "note";
public static final String CONTACT_IGNORED = "ignored";

// Create TABLES
public static final String CREATE_TABLE_BROADCASTLIST = "CREATE TABLE "
        + TABLE_BROADCASTLIST + "(" + BROADCASTLIST_ID
        + " INTEGER PRIMARY KEY AUTOINCREMENT, " + BROADCASTLIST_NAME
        + " TEXT, " + BROADCASTLIST_NOTE + " TEXT " + ")";
public static final String CREATE_TABLE_BROADCAST_MEMBERLIST = "CREATE TABLE "
        + TABLE_BROADCAST_MEMBERLIST
        + "("
        + ID
        + " INTEGER PRIMARY KEY AUTOINCREMENT, "
        + BROADCAST_MEMBERLIST_ID
        + " INTEGER, " + BROADCAST_MEMBER_ID + " TEXT " + ")";
public static final String CREATE_TABLE_SETTINGS = "CREATE TABLE "
        + TABLE_SETTINGS + "(" + SETTINGS_ID
        + " INTEGER PRIMARY KEY AUTOINCREMENT, " + SETTINGS_LANGUAGE
        + " TEXT, " + SETTINGS_FONT + " TEXT, " + SETTINGS_FONTSIZE
        + " TEXT, " + SETTINGS_BACKGROUND + " TEXT " + ")";
public static final String CREATE_TABLE_CHAT = "CREATE TABLE " + TABLE_CHAT
        + "(" + CHAT_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
        + CHAT_NAME + " TEXT, " + CHAT_MUTED + " INTEGER, " // 0 (false) 1
                                                            // (true)
        + CHAT_TYPE + " TEXT " + ")";
public static final String CREATE_TABLE_MESSAGELIST = "CREATE TABLE "
        + TABLE_MESSAGELIST + "(" + MESSAGELIST_ID
        + " INTEGER PRIMARY KEY AUTOINCREMENT, " + MESSAGELIST_CHAT_ID
        + "INTEGER, " + MESSAGE_TYPE + " TEXT, " + MESSAGE_CONTENT
        + " TEXT, " + MESSAGE_READ_FLAG
        + " INTEGER, " // 0 (false) 1 (true)
        + MESSAGE_DATE + " INTEGER, " + MESSAGE_RECEIVERLIST_ID
        + "INTEGER " + ")";
public static final String CREATE_TABLE_RECEIVERLIST = "CREATE TABLE "
        + TABLE_RECEIVERLIST + "(" + ID
        + " INTEGER PRIMARY KEY AUTOINCREMENT, " + RECEIVERLIST_ID
        + " INTEGER, " + RECEIVER_ID + " TEXT " + ")";
public static final String CREATE_TABLE_GROUP = " CREATE TABLE "
        + TABLE_GROUP + "(" + GROUP_ID
        + " INTEGER PRIMARY KEY AUTOINCREMENT, " + GROUP_CHAT_ID
        + " INTEGER, " + GROUP_NAME + " TEXT, " + GROUP_NOTE + " TEXT, "
        + GROUP_ADMIN + " TEXT " + ")";

public static final String CREATE_TABLE_GROUP_MEMBERLIST = "CREATE TABLE "
        + TABLE_GROUP_MEMBERLIST + "(" + ID
        + " INTEGER PRIMARY KEY AUTOINCREMENT, " + GROUP_MEMBERLIST_ID
        + " INTEGER, " + GROUP_MEMBER_ID + " TEXT " + ")";
public static final String CREATE_TABLE_CONTACTLIST = "CREATE TABLE "
        + TABLE_CONTACTLIST + "(" + CONTACT_ID + " TEXT PRIMARY KEY, "
        + CONTACT_CHAT_ID + "INTEGER, " + CONTACT_NAME + " TEXT, "
        + CONTACT_NOTE + " TEXT, " + CONTACT_IGNORED + " INTEGER " // 0
                                                                    // (false)
                                                                    // 1
                                                                    // (true)
        + ")";

/**
 * @param context
 * @param name
 * @param factory
 * @param version
 */
public DatabaseHelper(Context context) {
    super(context, DATABASE_NAME, null, DATABASE_VERSION);
}

/*
 * (non-Javadoc)
 * 
 * @see
 * android.database.sqlite.SQLiteOpenHelper#onCreate(android.database.sqlite
 * .SQLiteDatabase)
 */
@Override
public void onCreate(SQLiteDatabase database) {
    database.execSQL(CREATE_TABLE_BROADCASTLIST);
    database.execSQL(CREATE_TABLE_BROADCAST_MEMBERLIST);
    database.execSQL(CREATE_TABLE_SETTINGS);
    database.execSQL(CREATE_TABLE_CHAT);
    database.execSQL(CREATE_TABLE_MESSAGELIST);
    database.execSQL(CREATE_TABLE_RECEIVERLIST);

    database.execSQL(CREATE_TABLE_GROUP_MEMBERLIST);
    database.execSQL(CREATE_TABLE_CONTACTLIST);
    database.execSQL(CREATE_TABLE_GROUP);
}

/*
 * (non-Javadoc)
 * 
 * @see
 * android.database.sqlite.SQLiteOpenHelper#onUpgrade(android.database.sqlite
 * .SQLiteDatabase, int, int)
 */
@Override
public void onUpgrade(SQLiteDatabase database, int oldVersion,
        int newVersion) {
    database.execSQL("DROP TABLE IF EXISTS " + TABLE_BROADCASTLIST);
    database.execSQL("DROP TABLE IF EXISTS " + TABLE_BROADCAST_MEMBERLIST);
    database.execSQL("DROP TABLE IF EXISTS " + TABLE_SETTINGS);
    database.execSQL("DROP TABLE IF EXISTS " + TABLE_CHAT);
    database.execSQL("DROP TABLE IF EXISTS " + TABLE_MESSAGELIST);
    database.execSQL("DROP TABLE IF EXISTS " + TABLE_RECEIVERLIST);
    database.execSQL("DROP TABLE IF EXISTS " + TABLE_GROUP);
    database.execSQL("DROP TABLE IF EXISTS " + TABLE_GROUP_MEMBERLIST);
    database.execSQL("DROP TABLE IF EXISTS " + TABLE_CONTACTLIST);

    // recreate the tables
    onCreate(database);
}

}

When I run the application or a test in JUnit in log chat i get an error: With the Tag: SQLiteLog and Text (1) near "group": syntax error And in JUnit the same error:

android.database.sqlite.SQLiteException: near "group": syntax error (code 1): , while compiling: CREATE TABLE group(id INTEGER PRIMARY KEY AUTOINCREMENT, group_chat_id INTEGER, name TEXT, note TEXT, admin TEXT )

I wrote all group strings new to check if theire is a ' somewhere. When I remove database.execSQL(CREATE_TABLE_GROUP); the database is created as usual but i cant use the table group.

Maybe one of you see my mistake :)

group preserved keyword in SQLite so used different Name — M D
– M D, Commented Jul 1, 2015 at 11:24
Probably problem is occurring due to group table name because group is sqlite keyword so try to use different name — ρяσѕρєя K
– ρяσѕρєя K, Commented Jul 1, 2015 at 11:24
alright thanks for fast request, and i was wondering why i got this error :) — Sergen
– Sergen, Commented Jul 1, 2015 at 15:21

Community · Accepted Answer · 2017-05-23 12:07:39Z

2

It seems that group is a restricted sqlite word.

See answer Sqlite Exception, syntax error. It has the same exact problem.

edited May 23, 2017 at 12:07

CommunityBot

11 silver badge

answered Jul 1, 2015 at 11:27

Rui Santos

5473 silver badges15 bronze badges

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

Comments

Kushal · Accepted Answer · 2015-07-01 12:01:52Z

1

Change :

public static final String TABLE_GROUP = "group";

To :

public static final String TABLE_GROUP = "table_group";

group is keyword in SQL

answered Jul 1, 2015 at 12:01

Kushal

8,53810 gold badges66 silver badges83 bronze badges

Collectives™ on Stack Overflow

Syntax error in SQLite creating a table

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related