XxGoliathusxX XxGoliathusxX - 3 months ago 33
Android Question

Android Sqlite Foreign Key Unknwon Column

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 " + ");";

Answer

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.