SQLITE create table syntax error (code 1 SQLITE_ERROR[1])

Question

I need some help. This bug took me more hours than needed...

I have this query:

"CREATE TABLE IF NOT EXISTS staff_ratio (id INTEGER PRIMARY KEY AUTOINCREMENT, type INTEGER CHECK ( type IN (0, 1) ) NOT NULL DEFAULT 0, shift INTEGER CHECK ( shift IN (0, 2, 1)) NOT NULL DEFAULT 0, personal_count INTEGER, clients INTEGER, ratio REAL, date INTEGER NOT NULL, restaurant_id INTEGER NOT NULL, FOREIGN KEY(restaurant_id) REFERENCES restaurants(id) ON DELETE CASCADE, hotel_staff_ratio_id INTEGER NOT NULL, FOREIGN KEY(hotel_staff_ratio_id) REFERENCES hotel_staff_ratio(id) ON DELETE CASCADE)".

And the error: "android.database.sqlite.SQLiteException: near "hotel_staff_ratio_id": syntax error (code 1 SQLITE_ERROR[1])"

Answer 1

You're mixing up column specifications and table constraints.

Columns specifications such as hotel_staff_ratio_id INTEGER NOT NULL come first and table constraints such as FOREIGN KEY(restaurant_id) REFERENCES restaurants(id) ON DELETE CASCADE go last.

Move the hotel_staff_ratio_id INTEGER NOT NULL before the first FOREIGN KEY table constraint.