CODEWITHSUNDEEP
sqliteforeign-keys
Anna
Anna

Reputation: 409

Sqlite3: Error deleting record: foreign key mismatch

I am trying to delete a record from table users.

Tried deleteing it with a DELETE-statement (DELETE FROM users WHERE user_id=10" as well as in my DB browser, but I get the above error, specifically it says: "Error deleting record: foreign key mismatch - "games" referencing "groups" (DELETE FROM "main"."users" WHERE rowid IN ('10');)". Below my schema:

CREATE TABLE users ( user_id INTEGER PRIMARY KEY, name VARCHAR(255) NOT NULL, hash VARCHAR(255) NOT NULL, UNIQUE(name));

CREATE TABLE 'groups' ( 'group_name' VARCHAR(255) NOT NULL, 'turn' INTEGER NOT NULL, 'user_id'INTEGER, FOREIGN KEY ('user_id') REFERENCES 'users'('user_id') ON UPDATE CASCADE ON DELETE CASCADE );

CREATE TABLE games ( game_id INTEGER PRIMARY KEY, active INTEGER, turn INTEGER, group_name VARCHAR(255) NOT NULL, FOREIGN KEY (turn) REFERENCES groups(turn_id) ON UPDATE CASCADE ON DELETE CASCADE, FOREIGN KEY (group_name) REFERENCES groups(group_name) ON UPDATE CASCADE ON DELETE NO ACTION );

Why is this a problem? user_id is not even a foreign key in 'games'?? Thank you!

Upvotes: 0

Views: 1081

Answers (1)

Anna
Anna

Reputation: 409

The problem in the end was my DB Browser for SQLite (3.11.2) whose GUI allows deletions but they don't actually work. When I tied again in the bash and closed and restarted the DB Browser, the rows were gone. Uninsightful but figured I'd post nonetheless in case anyone else comes across this.

Upvotes: 2

Related Questions