App crashing when trying to insert values into sqlite database table

Question

I have created a database with few tables. When I call Database.java, the database get created(The app does not crashes).

When i try to insert into login table , the app crashes.I have no idea what to do next.I think the problem is with the table insertion code.

The table creation code(Database.java)

public static final String db="shg.db";
public static final int version=1;

public Database(Context context) {
    super(context, db, null, version);
}

@Override
public void onCreate(SQLiteDatabase db) {
    db.execSQL("create table if not exists login(id integer primary key autoincrement,log_name text,log_pass text)");
    db.execSQL("create table if not exists members(foreign key (id) REFERENCES login(id),mem_name text,mem_dob date,mem_address text,mem_phno text)");
    db.execSQL("create table if not exists committee(foreign key (id) REFERENCES login(id),committee_id int autoincrement,committee_date date)");
    db.execSQL("create table if not exists status(foreign key (id) REFERENCES login(id),loan_count int,thrift_complete int)");
    db.execSQL("create table if not exists attendance(foreign key (committee_id) REFERENCES login(committee_id),foreign key (id) REFERENCES login(id))");
    db.execSQL("create table if not exists loan(loan_id int,foreign key (committee_id) REFERENCES login(committee_id),foreign key (id) REFERENCES login(id),amount numeric)");
    db.execSQL("create table if not exists loanrepayment(foreign key (id) REFERENCES login(id),foreign key (loan_id) REFERENCES login(loan_id),foreign key(committee_id) REFERENCES login(committee_id),amount numeric,interest numeric)");
    db.execSQL("create table if not exists thrift(foreign key (committee_id) REFERENCES login(committee_id),foreign key (id) REFERENCES login(id),amount numeric)");
    db.execSQL("create table if not exists fine(foreign key (committee_id) REFERENCES login(committee_id),foreign key (id) REFERENCES login(id),amount numeric,reason text)");
}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
}

public boolean insertlogin(String name,String pass) {
    SQLiteDatabase db = this.getWritableDatabase();
    ContentValues cv = new ContentValues();
    cv.put("log_name",name);
    cv.put("log_pass",pass);
    db.insert("login",null,cv);
    return true;
}
public Cursor getlogin(String uname,String upass) {
    Cursor c;
    String qry="select * from login where log_name ="+uname+" and log_pass =" +upass+";";
    SQLiteDatabase db = this.getReadableDatabase();
    String[]col_name={"log_name","log_pass"};
    String whereClause = "log_name = ? OR log_pass = ?";
    String[] whereArgs = new String[] {uname, upass};
    c=db.query("login",col_name,whereClause,whereArgs,null,null,null);
    return c;
}

Database insertion code(MainActivity.java)

db=new Database(this);
Boolean status=db.insertlogin("a","b");
Toast.makeText(MainActivity.this, ""+status, Toast.LENGTH_SHORT).show();

Somehow i have managed to run the app on my device i have used during the phases of developing the project(Without crashing).When i tried to run the project on new device , the app crashes.

E/SQLiteLog: (1) near "foreign": syntax error
E/AndroidRuntime: FATAL EXCEPTION: main
Process: droid.dev.shg.shg, PID: 29705
java.lang.RuntimeException: Unable to start activity ComponentInfo{droid.dev.shg.shg/droid.dev.shg.shg.MainActivity}: android.database.sqlite.SQLiteException: near "foreign": syntax error (code 1): , while compiling: create table if not exists members(foreign key (id) REFERENCES login(id),mem_name text,mem_dob date,mem_address text,mem_phno text)
    at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2584)
    at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2666)
    at android.app.ActivityThread.handleRelaunchActivity(ActivityThread.java:4336)
    at android.app.ActivityThread.-wrap15(ActivityThread.java)
    at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1499)
    at android.os.Handler.dispatchMessage(Handler.java:111)
    at android.os.Looper.loop(Looper.java:207)
    at android.app.ActivityThread.main(ActivityThread.java:5769)
    at java.lang.reflect.Method.invoke(Native Method)
    at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:789)
    at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:679)
Caused by: android.database.sqlite.SQLiteException: near "foreign": syntax error (code 1): , while compiling: create table if not exists members(foreign key (id) REFERENCES login(id),mem_name text,mem_dob date,mem_address text,mem_phno text)
    at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
    at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:896)
    at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:507)
    at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
    at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
    at android.database.sqlite.SQLiteStatement.<init>(SQLiteStatement.java:31)
    at android.database.sqlite.SQLiteDatabase.executeSql(SQLiteDatabase.java:1704)
    at android.database.sqlite.SQLiteDatabase.execSQL(SQLiteDatabase.java:1635)
    at droid.dev.shg.shg.Database.onCreate(Database.java:22)
    at android.database.sqlite.SQLiteOpenHelper.getDatabaseLocked(SQLiteOpenHelper.java:251)
    at android.database.sqlite.SQLiteOpenHelper.getWritableDatabase(SQLiteOpenHelper.java:163)
    at droid.dev.shg.shg.Database.insertlogin(Database.java:39)
    at droid.dev.shg.shg.MainActivity$override.onCreate(MainActivity.java:30)
    at droid.dev.shg.shg.MainActivity$override.access$dispatch(MainActivity.java)
    at droid.dev.shg.shg.MainActivity.onCreate(MainActivity.java:0)
    at android.app.Activity.performCreate(Activity.java:6583)
    at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1114)
    at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2531)
    at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2666) 
    at android.app.ActivityThread.handleRelaunchActivity(ActivityThread.java:4336) 
    at android.app.ActivityThread.-wrap15(ActivityThread.java) 
    at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1499) 
    at android.os.Handler.dispatchMessage(Handler.java:111) 
    at android.os.Looper.loop(Looper.java:207) 
    at android.app.ActivityThread.main(ActivityThread.java:5769) 
    at java.lang.reflect.Method.invoke(Native Method) 
    at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:789) 
    at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:679)

Answer 1

Your "foreign key" need to be declared in the normal way, then assigned as a foreign key. For example:

create table members(id INTEGER, mem_name TEXT, FOREIGN KEY(id) REFERENCE login(id))

Answer 2

you must change your SQL queries to something like below:

create table if not exists members(id integer, mem_name text,mem_dob date,mem_address text,mem_phno text, FOREIGN KEY (id) REFERENCES login(id))

FOREIGN KEY must be define for exiting field.