Error in Foreign key

Question

1. CREATE TABLE IF NOT EXISTS users(id INTEGER PRIMARY KEY,uname TEXT,fname TEXT,lname TEXT,pwd TEXT,mailid TEXT)
2. CREATE TABLE IF NOT EXISTS group(groupid INTEGER PRIMARY KEY,groupname TEXT,userid INTEGER,groupdate TEXT,groupdescription TEXT,groupnote TEXT, FOREIGN KEY (userid) REFERENCES users(id))

PROGRAM

private static final String TABLE_USERS = "users";
private static final String TABLE_GROUP = "group";

// user Table Columns names

private static final String KEY_ID = "id";
private static final String KEY_USERNAME = "uname";
private static final String KEY_FIRSTNAME = "fname";
private static final String KEY_LASTNAME = "lname";
private static final String KEY_PASSWORD = "pwd";
private static final String KEY_EMAILID = "mailid";

// group Table Columns names

private static final String KEY_GPID = "groupid";
private static final String KEY_GNAME = "groupname";
private static final String KEY_GUSERID = "userid";
private static final String KEY_GDATE = "groupdate";
private static final String KEY_GDESCRIPTION = "groupdescription";
private static final String KEY_GNOTE = "groupnote";

private static final String TYPE_TEXT = " TEXT";
private static final String TYPE_INTEGER = " INTEGER";
private static final String TYPE_REAL = " REAL";
private static final String COMMA_SEP = ",";
private static final String REFER  = " REFERENCES ";
private static final String FOREIGN  = " FOREIGN KEY ";
private static final String LEFT_BRACKET  = "(";
private static final String RIGHT_BRACKET  = ")";


String CREATE_USER_TABLE = "CREATE TABLE IF NOT EXISTS " + TABLE_USERS + LEFT_BRACKET 
            + KEY_ID+ " INTEGER PRIMARY KEY," 
            + KEY_USERNAME + TYPE_TEXT+ COMMA_SEP 
            + KEY_FIRSTNAME + TYPE_TEXT + COMMA_SEP
            + KEY_LASTNAME + TYPE_TEXT + COMMA_SEP 
            + KEY_PASSWORD+ TYPE_TEXT + COMMA_SEP 
            + KEY_EMAILID + TYPE_TEXT + RIGHT_BRACKET;


String CREATE_GROUP_TABLE = "CREATE TABLE IF NOT EXISTS " + TABLE_GROUP + LEFT_BRACKET
            + KEY_GPID+ " INTEGER PRIMARY KEY," 
            + KEY_GNAME + TYPE_TEXT+ COMMA_SEP
            + KEY_GUSERID+ TYPE_INTEGER +COMMA_SEP
            + KEY_GDATE + TYPE_TEXT+ COMMA_SEP
            + KEY_GDESCRIPTION + TYPE_TEXT + COMMA_SEP 
            + KEY_GNOTE+ TYPE_TEXT +COMMA_SEP
            +FOREIGN+LEFT_BRACKET+KEY_GUSERID+RIGHT_BRACKET+REFER+TABLE_USERS+LEFT_BRACKET+KEY_ID+RIGHT_BRACKET+ RIGHT_BRACKET;

try
{
    db.execSQL(CREATE_USER_TABLE);
    db.execSQL(CREATE_GROUP_TABLE);
}
catch(SQLiteException e)
{
    e.printStackTrace();
}

Exception

android.database.sqlite.SQLiteException: near "group": syntax error (code 1): , while compiling: CREATE TABLE IF NOT EXISTS group(groupid INTEGER PRIMARY KEY,groupname TEXT,userid INTEGER,groupdate TEXT,groupdescription TEXT,groupnote TEXT, FOREIGN KEY (userid) REFERENCES users(id))

please tell me what solution of this pbm ??????? what is the pbm ????? why it happen ???????

Answer 1

Try this way,hope this will help you to solve your problem.

'group' and 'GROUP' is keyword in SQL / SQLite so you either change your table name or just add double quotes around group table name keyword like :

CREATE TABLE IF NOT EXISTS "group"(groupid INTEGER PRIMARY KEY,groupname TEXT,userid INTEGER,groupdate TEXT,groupdescription TEXT,groupnote TEXT, FOREIGN KEY (userid) REFERENCES users(id))

Answer 2

GROUP is a keyword in SQL. Either rename the table or put it in "double quotes".