String CREATE_FAVOURITES_TABLE = "CREATE TABLE IF NOT EXISTS " +
TABLE_FAVOURITES + "("
+ COLUMN_ID2 + " INTEGER PRIMARY KEY,"
+ COLUMN_NAME + " TEXT,"
+ COLUMN_POSTCODE + " INTEGER,"
+ COLUMN_DEELGEMEENTE + " TEXT,"
+ COLUMN_GEMEENTE + " TEXT,"
+ COLUMN_ADRES + " TEXT,"
+ COLUMN_USERNAME + " TEXT, "
+ "FOREIGN KEY(" + COLUMN_USERNAME+ ") REFERENCES "
+ TABLE_USERS + "(username) " + ")";
db.execSQL(CREATE_FAVOURITES_TABLE);
I tried to change this so I could link the two tables by the users username (otherwise I have to run a query to find the users username every time, and the usernames are unique so it's impossible for there to be two in my database, this is prevented by checks in my app). But 1) it's not working anymore, the last three lines of my database create statement are probably incorrect, could anyone check that?
And 2) I want to find all the favourites for a single user by username, so I wrote this piece of code:
public void findFavourites(User user,Winkel winkel){
String selectQuery = "SELECT" + COLUMN_NAME +"," + COLUMN_ADRES + "," + COLUMN_GEMEENTE + "," + COLUMN_DEELGEMEENTE + "," + COLUMN_POSTCODE;
selectQuery += "FROM" + TABLE_USERS + "," +TABLE_FAVOURITES;
selectQuery += "WHERE"+ COLUMN_ID+"=" +COLUMN_ID + "AND";
}
Again, the last line is probably incorrect because I can't figure out how to get the foreign keys working (they worked before, but then I tried to change them so username would be used as foreign key, and not id).
Error message:
SQLiteException: table favorieten has no column named username (code 1):
