0

I have two existing tables, workTable and table_jobs. I want to update my workTable and create a relationship with table_jobs with a default value. This is what I tried:

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    switch (oldVersion){
        case 3:{

            int jobid = insertJobWithoutClosing(new Job("Default", Color.rgb(0, 0, 255)), db);
            db.execSQL("ALTER TABLE " + workTable + " ADD COLUMN " + colJobID + " INTEGER DEFAULT " + jobid + " FOREIGN KEY("+colJobID+") REFERENCES "+table_jobs+"("+job_ID+")");

        }

    }

This gives me the following exception:

android.database.sqlite.SQLiteException: near "FOREIGN": syntax error (code 1): , while compiling: ALTER TABLE WorkObjects ADD COLUMN JobID INTEGER DEFAULT 1 FOREIGN KEY(JobID) REFERENCES Jobs(jobsID)

What is wrong?

Robin Dijkhof
  • 18,665
  • 11
  • 65
  • 116

2 Answers2

1

The FOREIGN KEY clause is a table constraint. ALTER TABLE does not support table constraints.

You have to make the foreign key constraint a column constraint:

ALTER TABLE WorkObjects
ADD COLUMN JobID INTEGER DEFAULT 1 REFERENCES Jobs(jobsID);

Error: Cannot add a REFERENCES column with non-NULL default value

Oh well. You have to recreate the table.

CL.
  • 173,858
  • 17
  • 217
  • 259
0

Here there is a well explained answer for this It seems foreign keys can be added only when you create the table. You can move your data in a new temp table, then recreate your original one with the foreign key constraint.

Community
  • 1
  • 1
Krasimir Stoev
  • 1,734
  • 11
  • 9