Getting NULL value in foreign key column

Question

I have two table (Information and WorkDetails) in SQLite where WorkDetails has a foreign key which refer to Information. When everytime the data inserted, the foreign key should follow the number of PK in Information. However, I get NULL value in foreign key column. The PK in Information and WorkDetails Table is auto-increment.

MyDatabaseAdapter.java

  public void onCreate(SQLiteDatabase db)
        {
           db.execSQL("create table "+TABLE_INFO+"(ID INTEGER PRIMARY KEY ,Name TEXT)");
           db.execSQL("create table"+TABLE_WORKDETAILS+"(ID INTEGER PRIMARY KEY , Project TEXT, WorkDescription TEXT, Per Text, TimeIn DATETIME, TimeOut DATETIME,TotalHours DATETIME, TableInfo_id INTEGER, FOREIGN KEY(TableInfo_id)REFERENCES TABLE_INFO(ID)");
        }

WorkDetailsTable.java

WD= new com.example.project.project.API.WorkDetailsAPI(this);
 ts= new com.example.project.project.API.InfoAPI(this);

    Button btn1=(Button)findViewById(R.id.button2);
        btn1.setOnClickListener(new View.OnClickListener() {
            public void onClick(View arg0) {
                W1=txtWork1.getText().toString();
                W2=txtWork2.getText().toString();
                W3=txtWork3.getText().toString();
                W4=txtWork4.getText().toString();
                a1 = spinnerTra.getSelectedItem().toString();
                a2= spinnerTra2.getSelectedItem().toString();
                a3 = spinnerTra3.getSelectedItem().toString();
                a4=spinnerTra4.getSelectedItem().toString();
                P1=per1.getText().toString();
                P2=per2.getText().toString();
                P3=per3.getText().toString();
                P4=per4.getText().toString();
                ts.insertTimeSheet(name); // refer to TimeSheetAPI
                WD.insertWorkDetails(a1,W1,P1,b,c,th); // insert multiple row and refer to WorkDetailsAPI
                WD.insertWorkDetails(a2,W2,P2,d,e1,th);
                WD.insertWorkDetails(a3, W3, P3, f, g,th);
                WD.insertWorkDetails(a4,W4,P4,h,i,th);
            }
        });

InfoAPI.java

   public class InfoAPI {
    private SQLiteDatabase database;
    private MyDatabaseHelper dbHelper;
    public String[] allColumns={MyDatabaseHelper.ID,MyDatabaseHelper.Name};

    public InfoAPI(Context context)
    {
        dbHelper=new MyDatabaseHelper(context);
    }

    public void open() throws SQLException {
        database = dbHelper.getWritableDatabase();

    }

    public void close() {
        dbHelper.close();
    }
    public long insertTimeSheet(String name)
    {
        database=dbHelper.getWritableDatabase();
        ContentValues values=new ContentValues();
        values.put(MyDatabaseHelper.Name,name);
        database.insert(MyDatabaseHelper.TABLE_INFO,null,values);
        database.close();
        return 0 ;

    }

}

WorkDetailsAPI.java

public class WorkDetailsAPI {
    private SQLiteDatabase database;
    private MyDatabaseHelper dbHelper;
    public String[] allColumns={MyDatabaseHelper.ID2,MyDatabaseHelper.Project,MyDatabaseHelper.WorkDescription,MyDatabaseHelper.Per,MyDatabaseHelper.TimeIn,MyDatabaseHelper.TimeOut,MyDatabaseHelper.TotalHours,MyDatabaseHelper.TableInfo_id};

    public WorkDetailsAPI(Context context)
    {
        dbHelper=new MyDatabaseHelper(context);
    }

    public void open() throws SQLException {
        database = dbHelper.getWritableDatabase();
    }

    public void close() {
        dbHelper.close();
    }
    public long insertWorkDetails(String project, String workDescription, String per,String timeIn,String timeOut,String totalHours)
    {
        database=dbHelper.getWritableDatabase();
        ContentValues values=new ContentValues();
        values.put(MyDatabaseHelper.Project,project);
        values.put(MyDatabaseHelper.WorkDescription,workDescription);
        values.put(MyDatabaseHelper.Per,per);
        values.put(MyDatabaseHelper.TimeIn,timeIn);
        values.put(MyDatabaseHelper.TimeOut,timeOut);
        values.put(MyDatabaseHelper.TotalHours, totalHours);
        database.insert(MyDatabaseHelper.TABLE_WORKDETAILS,null,values);
        database.close();
        return 0 ;

    }

}

you must include your tableinfo_id in your insertWorkDetails — Randyka Yudhistira, Oct 07 '15 at 07:59
SQLite is not a relational database. Here foreign key will not work. — Pankaj Kumar, Oct 07 '15 at 08:04
@John get your last inserted id and put it in your insert. `SELECT MAX(ID) FROM Table_Info` — Randyka Yudhistira, Oct 07 '15 at 08:56
@RandykaYudhistira Why need to use SELECT MAX? Can you work it out for me? Thanks — John, Oct 07 '15 at 08:58
@RandykaYudhistira actually they are inserted by button http://stackoverflow.com/questions/32983906/pass-data-to-three-activities-using-intent — John, Oct 07 '15 at 09:01

score 0 · Answer 1 · answered Oct 07 '15 at 08:10

0

You need to pass the inserted parent row id (TABLE_INFO ID) to your "insertWorkDetails" method after you actually inserted the parent row in "insertTimeSheet" method.

public long insertWorkDetails(Integer id, String project, String workDescription, String per,String timeIn,String timeOut,String totalHours){ ... }

Foreign keys won't get updated by themselves.

answered Oct 07 '15 at 08:10

Babak

419
2
16

I want to insert 4 rows in WorkDetaisTable by one click. Will the TableInfo_id follow the ID in Information? – John Oct 07 '15 at 08:15
`Integer lastInsertedId = ts.insertTimeSheet(name); WD.insertWorkDetails(lastInsertedId, a1,W1,P1,b,c,th); WD.insertWorkDetails(lastInsertedId, a2,W2,P2,d,e1,th); WD.insertWorkDetails(lastInsertedId, a3, W3, P3, f, g,th); WD.insertWorkDetails(lastInsertedId, a4,W4,P4,h,i,th);` – Babak Oct 07 '15 at 08:18

Randyka Yudhistira · Accepted Answer · 2015-10-07T09:19:04.900

0

change your insert method like this so you get your table_info id

public long insertTimeSheet(String name)
{
    database=dbHelper.getWritableDatabase();
    ContentValues values=new ContentValues();
    values.put(MyDatabaseHelper.Name,name);
    database.insert(MyDatabaseHelper.TABLE_INFO,null,values);
    Cursor cursor = database.rawQuery("SELECT MAX(ID) FROM Table_Info", null);
    database.close();
    return cursor.getLong(0) ;
}

and change your code

long id = ts.insertTimeSheet(name); // refer to TimeSheetAPI
WD.insertWorkDetails(a1,W1,P1,b,c,th,id);

your insertwd should

public long insertWorkDetails(String project, String workDescription, String per,String timeIn,String timeOut,String totalHours, long id)
{
    database=dbHelper.getWritableDatabase();
    ContentValues values=new ContentValues();
    values.put(MyDatabaseHelper.Project,project);
    values.put(MyDatabaseHelper.WorkDescription,workDescription);
    values.put(MyDatabaseHelper.Per,per);
    values.put(MyDatabaseHelper.TimeIn,timeIn);
    values.put(MyDatabaseHelper.TimeOut,timeOut);
    values.put(MyDatabaseHelper.TotalHours, totalHours);
    values.put("TableInfo_id", id);
    database.insert(MyDatabaseHelper.TABLE_WORKDETAILS,null,values);
    database.close();
    return 0 ;

}

edited Oct 07 '15 at 09:19

answered Oct 07 '15 at 09:03

Randyka Yudhistira

3,612
1
26
41

how about the workDetailsTable? Should I need to add anything? – John Oct 07 '15 at 09:09
At this line, WD.insertWorkDetails(a1,W1,P1,b,c,th,ab); under ab has a red line(wrong 7th arguments type.Found long required) – John Oct 07 '15 at 09:15
@John edited. why you cannot figure it by yourself? -_- are you just copy paste your code from other site – Randyka Yudhistira Oct 07 '15 at 09:20
I still new on android development and have no idea on how to insert the FK value – John Oct 07 '15 at 09:27
When I click the button, it crashed. java.lang.IllegalStateException: Cannot perform this operation because the connection pool has been closed. at android.database.sqlite.SQLiteConnectionPool.throwIfClosedLocked(SQLiteConnectionPool.java:962) at android.database.sqlite.SQLiteConnectionPool.waitForConnection(SQLiteConnectionPool.java:599) at android.database.sqlite.SQLiteConnectionPool.acquireConnection(SQLiteConnectionPool.java:348) – John Oct 07 '15 at 09:49
I have changed 'Cursor cursor = database.rawQuery("SELECT MAX(ID) FROM Table_Info", null);' to 'Cursor cursor = database.rawQuery("SELECT MAX(ID) FROM "+ MyDatabaseHelper.TABLE_INFO, null);' – John Oct 07 '15 at 09:57

Getting NULL value in foreign key column

2 Answers2

Linked