Android Sqlite Foreign Key Unknwon Column

0
votes

I added foreign keys to my database. I reinstalled the app from my device and increased the database version. Here is my code:

Constant: private static final String COLUMN_K_ID = "kid";

Create Tables:

  private static final String CREATE_TABLE_ZIELE_KOERPER =  "CREATE TABLE " + TABLE_ZIELE_KOERPER + "(" +
            COLUMN_ID           + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
            COLUMN_OPTION       + " INTEGER, "   +
            COLUMN_TENDENZ      + " INTEGER );";

    private static final String CREATE_TABLE_ZIELE_CARDIO =  "CREATE TABLE " + TABLE_ZIELE_CARDIO + "(" +
            COLUMN_ID           + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
            COLUMN_EXTRA        + " TEXT );";

    private static final String CREATE_TABLE_ZIELE_TRAINING =  "CREATE TABLE " + TABLE_ZIELE_TRAINING + "(" +
            COLUMN_ID           + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
            COLUMN_EXTRA        + " TEXT, "      +
            COLUMN_MUSKEL       + " TEXT, "      +
            COLUMN_WIEDERHOLUNGEN + " INTEGER )";

    private static final String CREATE_TABLE_ZIELE = "CREATE TABLE " + TABLE_ZIELE + "(" +
            COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
            "FOREIGN KEY(" + COLUMN_K_ID + ") REFERENCES " + TABLE_ZIELE_KOERPER      + "(" + COLUMN_ID + ")  ON DELETE CASCADE, " +
            "FOREIGN KEY(" + COLUMN_C_ID + ") REFERENCES " + TABLE_ZIELE_CARDIO       + "(" + COLUMN_ID + ")  ON DELETE CASCADE, " +
            "FOREIGN KEY(" + COLUMN_T_ID + ") REFERENCES " + TABLE_ZIELE_TRAINING     + "(" + COLUMN_ID + ")  ON DELETE CASCADE, " +
            COLUMN_NAME         + " TEXT, "      +
            COLUMN_ERSTELLDATUM + " LONG, "      +
            COLUMN_DEADLINE     + " LONG, "      +
            COLUMN_DONE         + " INTEGER, "   +
            COLUMN_VALUE        + " FLOAT, "     +
            COLUMN_FORTSCHRITT  + " FLOAT "      +
            ");";

@Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(CREATE_TABLE_ZIELE_KOERPER);
        db.execSQL(CREATE_TABLE_ZIELE_CARDIO);
        db.execSQL(CREATE_TABLE_ZIELE_TRAINING);
        db.execSQL(CREATE_TABLE_ZIELE);
    }

Log:

unknown column "kid" in foreign key definition (code 1): , while compiling: CREATE TABLE ziele(id INTEGER PRIMARY KEY AUTOINCREMENT, FOREIGN KEY(kid) REFERENCES koerperziele(id) ON DELETE CASCADE, FOREIGN KEY(cid) REFERENCES cardioziele(id) ON DELETE CASCADE, FOREIGN KEY(t_id) REFERENCES trainingsziele(id) ON DELETE CASCADE, name TEXT, erstelldatum LONG, deadline LONG, done INTEGER, value FLOAT, fortschritt FLOAT );

Activate foreign keys:

 @Override
    public void onOpen(SQLiteDatabase db) {
        super.onOpen(db);
        db.execSQL("PRAGMA foreign_keys=ON");
    }

EDIT: Still not working

private static final String CREATE_TABLE_ZIELE = "CREATE TABLE " + TABLE_ZIELE + "(" +
            COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
            COLUMN_NAME         + " TEXT, "      +
            COLUMN_ERSTELLDATUM + " LONG, "      +
            COLUMN_DEADLINE     + " LONG, "      +
            COLUMN_DONE         + " INTEGER, "   +
            COLUMN_VALUE        + " FLOAT, "     +
            COLUMN_FORTSCHRITT  + " FLOAT, "     +
            "FOREIGN KEY(" + COLUMN_K_ID + " INTEGER) REFERENCES " + TABLE_ZIELE_KOERPER      + "(" + COLUMN_ID + ")  ON DELETE CASCADE, " +
            "FOREIGN KEY(" + COLUMN_C_ID + " INTEGER) REFERENCES " + TABLE_ZIELE_CARDIO       + "(" + COLUMN_ID + ")  ON DELETE CASCADE, " +
            "FOREIGN KEY(" + COLUMN_T_ID + " INTEGER) REFERENCES " + TABLE_ZIELE_TRAINING     + "(" + COLUMN_ID + ")  ON DELETE CASCADE  " + ");";
2
androidsqliteexceptionkey

2 Answers

2
votes

Your CREATE TABLE for TABLE_ZIELE has two kinds of problems:

  1. You need to actually specify the columns you use as foreign keys, e.g. cid INTEGER.

  2. Constraints such as FOREIGN KEYs go after column specifications part. Move them at the end.

2
votes

You have to implement FOREIGN KEY as a table or column constraint. In the first case which you chose, you need to reference a column you defined before.

In the documentation you find some examples for how to do it correctly.

In your case you'd need to use the column constraints instead, which it looks like you intended to do.

You can just replace the lines like

"FOREIGN KEY(" + COLUMN_K_ID + ") REFERENCES " + TABLE_ZIELE_KOERPER + "(" + COLUMN_ID + ")  ON DELETE CASCADE, " +

with

COLUMN_K_ID + " REFERENCES " + TABLE_ZIELE_KOERPER + "(" + COLUMN_ID + ")  ON DELETE CASCADE, " +