CODEWITHSUNDEEP
sqlite
Amir Kian
Amir Kian

Reputation: 313

The child's keys don't get deleted when I delete the parent key

I made a database via SQLite which looks like the following:

enter image description here

and I created a table called as books like this:

CREATE TABLE books (id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, "title-book" TEXT, witer TEXT, comment TEXT, "intro-img" TEXT, "avalable-count" integer, "total-count" integer, "publisher-id" INTEGER REFERENCES publisher (id) ON DELETE CASCADE ON UPDATE CASCADE, "category-id" INTEGER REFERENCES categories (id) ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY ("publisher-id") REFERENCES publisher (id) ON DELETE CASCADE ON UPDATE CASCADE);

When I delete a category that is a parent key for books category-id then I see books table having the child key rows that include that category didn't remove and they exist as they were like in the following pictures:

enter image description here enter image description here

Upvotes: 0

Views: 458

Answers (1)

LordNeo
LordNeo

Reputation: 1182

SQLite 3.6.19 and older doesn't support Foreign Keys. If you have a newer version you can check the support using:

PRAGMA foreign_keys

If it returns "no data" it means there is no support installed (because it was compiled with SQLITE_OMIT_FOREIGN_KEY or SQLITE_OMIT_TRIGGER defined), while returning 0 means "supported but disabled" (default) and 1 means "supported and enabled".

You can enable at runtime using

PRAGMA foreign_keys = ON;

Be aware this must be enabled for every database connection:

Foreign key constraints are disabled by default (for backwards compatibility), so must be enabled separately for each database connection.

Also consider:

It is not possible to enable or disable foreign key constraints in the middle of a multi-statement transaction (when SQLite is not in autocommit mode). Attempting to do so does not return an error; it simply has no effect.

REFERENCE: https://sqlite.org/foreignkeys.html

Upvotes: 1

Related Questions