Foreign key isnt created on android studio but tables is created

I have 2 tables : registereduser, listbands. listbands has foreign key referencing registereduser(id)

I managed to create both tables in the databases, however when i create a new user the listbands fields are empty! It should have the id of registereduser.

Here is my code :

public class DatabaseHelper extends SQLiteOpenHelper {

    //User table
    public static final String DATABASE_NAME ="bands.db";
    public static final String TABLE_USER ="registereduser";
    public static final String COL_USER_ID ="BID";
    public static final String COL_USER_EMAIL ="email";
    public static final String COL_USER_PASS ="password";

    //bands table
    public static final String TABLE_LIST ="listbands";
    public static final String COL_LIST_ID ="LID";
    public static final String COL_LIST_FK ="BID";
    public static final String COL_LIST_NAME ="name";


    public DatabaseHelper(@Nullable Context context ) {
        super(context, DATABASE_NAME, null , 1);
    }

    @Override
    public void onCreate(SQLiteDatabase sqLiteDatabase) {

        sqLiteDatabase.execSQL("CREATE TABLE registereduser (BID INTEGER PRIMARY KEY AUTOINCREMENT, " +
                "email TEXT , password TEXT )");
        sqLiteDatabase.execSQL("CREATE TABLE listbands (LID INTEGER PRIMARY KEY AUTOINCREMENT ," +
                "BID INTEGER NOT NULL, name TEXT, FOREIGN KEY(BID) REFERENCES registereduser(BID) )");



    }

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

        sqLiteDatabase.execSQL("DROP TABLE IF EXISTS " + TABLE_USER);
        sqLiteDatabase.execSQL("DROP TABLE IF EXISTS " + TABLE_LIST);
        onCreate(sqLiteDatabase);

    }

    @Override
    public void onOpen(SQLiteDatabase sqLiteDatabase) {
        super.onOpen(sqLiteDatabase);

        //enable foreign key constraints like ON UPDATE CASCADE, ON DELETE CASCADE
        sqLiteDatabase.execSQL("PRAGMA foreign_keys=ON;");
    }

    public long addUser(String email, String password) {

        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues contentValues = new ContentValues();
        contentValues.put(" email ",email);
        contentValues.put(" password ",password);
        long res = db.insert("registereduser", null, contentValues);
        db.close();
        return res;
    }

    public long addList(String name) {

        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues contentLists = new ContentValues();
        contentLists.put(" name ",name);
        long rest = db.insert("listbands", null, contentLists);
        db.close();
        return rest;
    }

    public boolean checkUser ( String email, String password){

        String[] column = {COL_USER_ID};
        SQLiteDatabase db = getReadableDatabase();
        String choice = COL_USER_EMAIL + "=?" + " and " + COL_USER_PASS + "=?";
        String[] choiceArgs = { email, password};
        Cursor cursor = db.query(TABLE_USER, column, choice, choiceArgs, null, null, null);
        int count = cursor.getCount();
        cursor.close();
        db.close();

        if (count>0)
            return true;
        else
            return false;

    }
}

and this is the screenshoot that my listbands is empty: this is screenshoot from sqlite manager

Solution

Defining a Foreign Key doesn't automatically insert data into the child table (listbands table) when a row is added to the parent table (registereduser table).

That is a Foreign Key defines a constraint (rule) saying that the value of the child column(s) must be in a row of the parent table's column(s) when inserting or updating rows in the child table.

You need to add the rows to the child table as and when required.

What the screenshot is showing is that there are now rows in the listBands table.

Currently your code has an addList method this will not work as it is as the result will be a NOT NULL constraint conflict. However, it won't fail as the exception is trapped by the insert method. However if you were to look at the value returned it would be -1 rather than 1 or greater (the id of the inserted row).

You would need to use something like :-

public long addListProper(String name, long id) {
    SQLiteDatabase db = this.getWritableDatabase();
    ContentValues cv = new ContentValues();
    cv.put(COL_LIST_FK,id);
    cv.put(COL_LIST_NAME,name);
    return db.insert(TABLE_LIST,null,cv);
}

to insert a listbands row.

Example

Consider the following which :-

Deletes all rows form the listbands table.
Deletes all rows from the registereduser table.
- Note that as registereduser may include parents of listbands rows then listbands should be deleted first.
- These two steps are just to make it so the tables are empty each time the demo is run.
Adds a user
Adds a list (that WILL NOT be added)
Adds a list using the alternative/proper insert that uses the ID of the user
Logs info regrading the id's of the inserts.
Extracts the rows from the tables into a cursor which is dumped to the log.

The code :-

    mDBHlpr = new DatabaseHelper(this);
    mDBHlpr.getWritableDatabase().delete(DatabaseHelper.TABLE_LIST,null,null);
    mDBHlpr.getWritableDatabase().delete(DatabaseHelper.TABLE_USER,null,null);
    long this_user = mDBHlpr.addUser("Fred","1234567890");
    long this_list = mDBHlpr.addList("Blah"); //<<<<<<<<<< WILL NOT WORK
    long alt_list = mDBHlpr.addListProper("blah blah",this_user); //<<<<<<<<< SHOULD WORK
    Log.d("INSERTIFO","ID of user was " + this_user + " ID of list was " + this_list);
    DatabaseUtils.dumpCursor(
            mDBHlpr.getWritableDatabase().query(DatabaseHelper.TABLE_USER,null,null,null,null,null,null)
    );
    DatabaseUtils.dumpCursor(
            mDBHlpr.getWritableDatabase().query(DatabaseHelper.TABLE_LIST,null,null,null,null,null,null)
    );

Results

First in the log will be :-

2019-10-27 12:45:00.152 32338-32338/aso.so58575523foreignkeys E/SQLiteDatabase: Error inserting  name =Blah
    android.database.sqlite.SQLiteConstraintException: NOT NULL constraint failed: listbands.BID (code 1299 SQLITE_CONSTRAINT_NOTNULL)
        at android.database.sqlite.SQLiteConnection.nativeExecuteForLastInsertedRowId(Native Method)
        at android.database.sqlite.SQLiteConnection.executeForLastInsertedRowId(SQLiteConnection.java:879)
        at android.database.sqlite.SQLiteSession.executeForLastInsertedRowId(SQLiteSession.java:790)
        at android.database.sqlite.SQLiteStatement.executeInsert(SQLiteStatement.java:88)
        at android.database.sqlite.SQLiteDatabase.insertWithOnConflict(SQLiteDatabase.java:1599)
        at android.database.sqlite.SQLiteDatabase.insert(SQLiteDatabase.java:1468)
        at aso.so58575523foreignkeys.DatabaseHelper.addList(DatabaseHelper.java:76)
        at aso.so58575523foreignkeys.MainActivity.onCreate(MainActivity.java:22)
        at android.app.Activity.performCreate(Activity.java:7802)
        at android.app.Activity.performCreate(Activity.java:7791)
        at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1299)
        at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:3245)
        at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:3409)
        at android.app.servertransaction.LaunchActivityItem.execute(LaunchActivityItem.java:83)
        at android.app.servertransaction.TransactionExecutor.executeCallbacks(TransactionExecutor.java:135)
        at android.app.servertransaction.TransactionExecutor.execute(TransactionExecutor.java:95)
        at android.app.ActivityThread$H.handleMessage(ActivityThread.java:2016)
        at android.os.Handler.dispatchMessage(Handler.java:107)
        at android.os.Looper.loop(Looper.java:214)
        at android.app.ActivityThread.main(ActivityThread.java:7356)
        at java.lang.reflect.Method.invoke(Native Method)
        at com.android.internal.os.RuntimeInit$MethodAndArgsCaller.run(RuntimeInit.java:492)
        at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:930)

i.e. the addList has failed because BID column is null and thus the row is not inserted because of the NOT NULL constraint. Although the stack-trace is in the log the exception was trapped and thus processing continues.

The Log will then have :-

2019-10-27 12:45:00.165 D/INSERTIFO: ID of user was 3 ID of list was -1 ID of alternative list item was 2

see how the attempt to use addList resulted in an id of -1.

then :-

2019-10-27 12:45:00.165 I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@561c44
2019-10-27 12:45:00.166 I/System.out: 0 {
2019-10-27 12:45:00.166 I/System.out:    BID=3
2019-10-27 12:45:00.166 I/System.out:    email=Fred
2019-10-27 12:45:00.166 I/System.out:    password=1234567890
2019-10-27 12:45:00.166 I/System.out: }
2019-10-27 12:45:00.166 I/System.out: <<<<<
2019-10-27 12:45:00.167 I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@1def12d
2019-10-27 12:45:00.167 I/System.out: 0 {
2019-10-27 12:45:00.167 I/System.out:    LID=2
2019-10-27 12:45:00.167 I/System.out:    BID=3
2019-10-27 12:45:00.167 I/System.out:    name=blah blah
2019-10-27 12:45:00.167 I/System.out: }
2019-10-27 12:45:00.167 I/System.out: <<<<<

As can be seen the 2nd attempt (using addListProper) has added a row to listbands and there was no Foreign Key constraint conflict or the NOT NULL constraint conflict that originally stopped the row being added.

Extra

consider this line of code :-

long otheralter_list = mDBHlpr.addListProper("not blah",100); //<<<<<<< FK constraint as no user with an ID of 100.

A row is trying to be added that references a user with an id of 100 (no such user exists). The row will not be inserted (but again will not result in a failure) because of the Foreign Key constraint as no row in the registereduser table (the parent table of the Foreign Key) has 100 in the BID column (the parent column).

The result in the log would be :-

2019-10-27 13:12:39.272 32564-32564/? E/SQLiteDatabase: Error inserting BID=100 name=not blah
    android.database.sqlite.SQLiteConstraintException: FOREIGN KEY constraint failed (code 787 SQLITE_CONSTRAINT_FOREIGNKEY)
        at android.database.sqlite.SQLiteConnection.nativeExecuteForLastInsertedRowId(Native Method)
        at android.database.sqlite.SQLiteConnection.executeForLastInsertedRowId(SQLiteConnection.java:879)
        at android.database.sqlite.SQLiteSession.executeForLastInsertedRowId(SQLiteSession.java:790)
        at android.database.sqlite.SQLiteStatement.executeInsert(SQLiteStatement.java:88)
        at android.database.sqlite.SQLiteDatabase.insertWithOnConflict(SQLiteDatabase.java:1599)
        at android.database.sqlite.SQLiteDatabase.insert(SQLiteDatabase.java:1468)
        at aso.so58575523foreignkeys.DatabaseHelper.addListProper(DatabaseHelper.java:86)
        at aso.so58575523foreignkeys.MainActivity.onCreate(MainActivity.java:24)
        at android.app.Activity.performCreate(Activity.java:7802)
        at android.app.Activity.performCreate(Activity.java:7791)
        at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1299)
        at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:3245)
        at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:3409)
        at android.app.servertransaction.LaunchActivityItem.execute(LaunchActivityItem.java:83)
        at android.app.servertransaction.TransactionExecutor.executeCallbacks(TransactionExecutor.java:135)
        at android.app.servertransaction.TransactionExecutor.execute(TransactionExecutor.java:95)
        at android.app.ActivityThread$H.handleMessage(ActivityThread.java:2016)
        at android.os.Handler.dispatchMessage(Handler.java:107)
        at android.os.Looper.loop(Looper.java:214)
        at android.app.ActivityThread.main(ActivityThread.java:7356)
        at java.lang.reflect.Method.invoke(Native Method)
        at com.android.internal.os.RuntimeInit$MethodAndArgsCaller.run(RuntimeInit.java:492)
        at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:930)

Additional Re comment

So i must use CONSTRAINT in the listbands table to create a foreign key?

No you have correctly defined the Foreign Key. CONSTRAINT ???? FOREIGN KEY just gives the Constraint a name. It doesn't alter what the constraint does.

The listbands table also dont have ID even though its created as AUTOINCREMENT

No. The screen shots shows that there is no data at all in the listbands table (due to the NOT NULL constraint as explained above).

AUTOINCREMENT does very little INTEGER PRIMARY KEY is what allows an auto-generated value to be provided. Although this isn't actually correct as all that INTEGER PRIMARY KEY does is make the column an alias of the normally hidden rowid column, which is an automatically generated column for all tables except those defined using WITHOUT ROWID.

AUTOINCREMENT supplements the alias with an additional rule, that SQLite will adhere to if possible saying that the automatically generated value is greater then any previously allocated. Without AUTOINCREMENT and IF the largest possible value of the rowid (9223372036854775807) has been used then a lower value may be used. Without AUTOINCREMENT then SQLIte will instead issue an SQLITE_FULL exception.

AUTOINCREMENT uses an additional table, sqlite_sequence, to store the last assigned value of the rowid. As such inefficiencies are introduced in managing this extra table.

If AUTOINCREMENT WERE removed then there would be no noticeable difference.