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

Issue

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])"

Solution

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.

Answered By – laalto

Answer Checked By – Willingham (AngularFixing Volunteer)

Leave a Reply

Your email address will not be published.