How to insert values with Foreign KEY in SQLite?

Question

I am new with Android Sqlite Database. I have created a database with SQLite in Android and I have a table Student_details which use a foreign key of the table: Student, I have made the id as AUTOINCREMENT. And i tried to return the value of the AUTOINCREMENT Id from Student table.

But I am stuck to use the return values to insert into COLUMN_FR_KEY_USER_ID = "student_id"; as Foreign Key values.

How can I add value with a foreign key from another table using ContentValues into insetDataIntoStudentDetails method?

DatabaseHelper.java

public class DatabaseHelper extends SQLiteOpenHelper {
    private static final String DATABASE_NAME = "finalStudent.db";
    private static final int VERSION_NUMBER = 16;
    private static final String TABLE_STUDENT = "Student";
    private static final String TABLE_DETAILS_NAME = "Student_details";

    private static final String COLUMN_STUDENT_ID = "id";
    private static final String COLUMN_DETAILS_ID = "id";
    private static final String COLUMN_FR_KEY_USER_ID = "student_id";
    private static final String COLUMN_STUDENT_NAME = "name";
    private static final String COLUMN_DETAILS_NAME = "name";
    private static final String COLUMN_STUDENT_EMAIL = "email";
    private static final String COLUMN_STUDENT_PASSWORD = "password";

    private static final String CREATE_STUDENT_TABLE = "CREATE TABLE "+TABLE_STUDENT+"( "+COLUMN_STUDENT_ID+" INTEGER PRIMARY KEY AUTOINCREMENT, "+COLUMN_STUDENT_NAME+" TEXT, "+COLUMN_STUDENT_EMAIL+" TEXT, "+COLUMN_STUDENT_PASSWORD+" TEXT)";
    private static final String CREATE_DETAILS_TABLE = "CREATE TABLE "+TABLE_DETAILS_NAME+"( "+COLUMN_DETAILS_ID+" INTEGER PRIMARY KEY AUTOINCREMENT, "+COLUMN_DETAILS_NAME+" TEXT, "+COLUMN_FR_KEY_USER_ID+" INTEGER, FOREIGN KEY("+COLUMN_FR_KEY_USER_ID+") REFERENCES "+TABLE_STUDENT+" ("+COLUMN_STUDENT_ID+") ON UPDATE CASCADE ON DELETE CASCADE)";

    private Context context;
    private static final String DROP_TABLE = "DROP TABLE IF EXISTS "+TABLE_STUDENT;
    private static final String DROP_DETAILS_TABLE = "DROP TABLE IF EXISTS "+TABLE_DETAILS_NAME;


    public DatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, VERSION_NUMBER);
        this.context = context;
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        try {
            Toast.makeText(context,"Table created successfully and called onCreate method",Toast.LENGTH_SHORT).show();
            db.execSQL(CREATE_STUDENT_TABLE);
            db.execSQL(CREATE_DETAILS_TABLE);
        }catch (Exception e){
            Toast.makeText(context,"Exception : "+e,Toast.LENGTH_SHORT).show();
        }
    }

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

            Toast.makeText(context,"Table created successfully and called onCreate method",Toast.LENGTH_SHORT).show();
            db.execSQL(DROP_TABLE);
            db.execSQL(DROP_DETAILS_TABLE);
            onCreate(db);
        }catch (Exception e){
            Toast.makeText(context,"Exception : "+e,Toast.LENGTH_SHORT).show();
        }

    }

    public long insertDataIntoStudent( String name, String email, String password){
        // to write or read data in database , we have to call getWritableDatabase
        SQLiteDatabase sqLiteDatabase = this.getWritableDatabase();
        // we have to call contentValues class to store data in the data

        ContentValues contentValues = new ContentValues();
        contentValues.put(COLUMN_STUDENT_NAME,name);
        contentValues.put(COLUMN_STUDENT_EMAIL,email);
        contentValues.put(COLUMN_STUDENT_PASSWORD,password);

        // Insert the new row, returning the primary key value of the new row
        long newRowId = sqLiteDatabase.insert(TABLE_STUDENT,null,contentValues);
        return newRowId;
    }


    public void insetDataIntoStudentDetails(String name){

        SQLiteDatabase sqLiteDatabase = this.getWritableDatabase();
        ContentValues contentValues = new ContentValues();
       // TODO: insert the foreign key's value 
        contentValues.put(name,name);
    }


    @Override
    public void onOpen(SQLiteDatabase db) {
        super.onOpen(db);
        //enable foreign key constraints like ON UPDATE CASCADE, ON DELETE CASCADE
        db.execSQL("PRAGMA foreign_keys=ON;");
    }
}

MainActivity.java

public class MainActivity extends AppCompatActivity {

    private final AppCompatActivity activity = MainActivity.this;
    DatabaseHelper databaseHelper;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        databaseHelper = new DatabaseHelper(activity);
        databaseHelper.insertDataIntoStudent("Bappy","abcd@gmail.com","145456");
    }
    }

While I know this doesn't answer your question, you might want to evaluate using Room since you are using a database. — Bink, Feb 23 '20 at 17:46
@Bink thanks for the kind suggestion, but i have no idea about ```room``` , I mean how it works or other staff of ```room```. — Md Azharul Islam Somon, Feb 24 '20 at 03:01
You can find more detail about the Room Persistence Library at https://developer.android.com/topic/libraries/architecture/room. — Bink, Feb 24 '20 at 13:07
@Bink well, i got some understanding tutorial about ```Room```. It's really amazing and easier. — Md Azharul Islam Somon, Feb 25 '20 at 15:00

score 0 · Answer 1 · answered Feb 23 '20 at 18:32

I'll answer about databases in general, not specifically about Sqlite or it's usage in Android.

So you have student's name (which is not a primary key), but you need to save some additional data for this student to another table. The steps are:

Execute SELECT id FROM students WHERE name = ? to find the relevant identifier.
Execute INSERT INTO student_details for your secondary table using retrieved id as a key.

Extra tips:

Name is not a good unique identifier. It's good to have one more criteria - such as some group name, or group number or even date of birth.
When your database is large enough you may benefit from index for your search criteria to optimize execution of the first query. But usually it's not the case for client-side (in-app) databases.
Here we assume that name is not the data which could be updated by someone (or something) else between searching for id and executing INSERT. Otherwise it's wise to use transaction and "lock" your student until you complete the updates.

Thanks, i am new in that area, it will help me if i get specifically about ```Sqlite``` in Android. — Md Azharul Islam Somon, Feb 24 '20 at 03:03

How to insert values with Foreign KEY in SQLite?

1 Answers1