To explain my predicament, I have the following schema

BEGIN TRANSACTION;
CREATE TABLE IF NOT EXISTS "manager" (
  "manager_id"  INTEGER,
  "name"  TEXT,
  "phone" TEXT,
  "email" TEXT,
  PRIMARY KEY("manager_id")
);
CREATE TABLE IF NOT EXISTS "client" (
  "client_id" INTEGER,
  "name"  TEXT,
  "phone" TEXT,
  "address" TEXT,
  "email" TEXT,
  PRIMARY KEY("client_id")
);
CREATE TABLE IF NOT EXISTS "booking" (
  "booking_id"  INTEGER,
  "client_id" INTEGER,
  "hall_id" INTEGER,
  "slot"  TEXT,
  "date"  TEXT,
  PRIMARY KEY("booking_id"),
  FOREIGN KEY("hall_id") REFERENCES "hall"("hall_id"),
  FOREIGN KEY("client_id") REFERENCES "client"("client_id")
);
CREATE TABLE IF NOT EXISTS "hall" (
  "hall_id" INTEGER,
  "name"  TEXT,
  "phone" TEXT,
  "address" TEXT,
  "rent"  INTEGER,
  "size"  TEXT,
  "manager_id"  INTEGER,
  PRIMARY KEY("hall_id"),
  FOREIGN KEY("manager_id") REFERENCES "manager"("manager_id")
);
COMMIT;

and I have the following PHP code

$q = "DELETE FROM manager where manager_id = ?";
$stmt = $pdo->prepare($q);
$stmt->execute([$manager_id]);
$result = $stmt->rowCount();

My problem is that despite the foreign keys this seems to delete a manager even if he is still assigned to a hall.

Execute the statement pragma foreign_keys as a query. The returned value for the column named "foreign_keys" will be either 0 (enforcement disabled) or 1 (enforcement enabled).

One trusts that you have read https://sqlite.org/pragma.html#pragma_foreign_keys and are complying with the requirement This pragma is a no-op within a transaction; and you are fiddling with the setting outside of any transaction?

Thanks. Yes, the exec comes directly after setting $pdo.

I am okay with simple databases but have never used foreign keys before.

OP, don't know this helps or not, but I pasted your schema into the CLI (in my case, into sqlite3.exe on Windows, version 3.39.2) followed by the trivial test below. As expected, my attempt to delete a manager with an assigned hall failed.

Are you certain that foreign keys are enabled and that a manager was inappropriately deleted?

.mode box pragma foreign_keys = 1; pragma foreign_keys; -- confirm current state. insert into manager values(99, 'Clark Kent', '', ''); insert into hall values(2, 'Monty Hall', '', '', '', 'big', 99); delete from manager where manager_id=99; SELECT * FROM hall; SELECT * from manager;

And the results follow below:

sqlite> .mode box sqlite> pragma foreign_keys = 1; sqlite> pragma foreign_keys; -- confirm current state. ┌──────────────┐ │ foreign_keys │ ├──────────────┤ │ 1 │ └──────────────┘ sqlite> insert into manager values(99, 'Clark Kent', '', ''); sqlite> insert into hall values(2, 'Monty Hall', '', '', '', 'big', 99); sqlite> delete from manager where manager_id=99; Runtime error: FOREIGN KEY constraint failed (19) sqlite> SELECT * FROM hall; ┌─────────┬────────────┬───────┬─────────┬──────┬──────┬────────────┐ │ hall_id │ name │ phone │ address │ rent │ size │ manager_id │ ├─────────┼────────────┼───────┼─────────┼──────┼──────┼────────────┤ │ 2 │ Monty Hall │ │ │ │ big │ 99 │ └─────────┴────────────┴───────┴─────────┴──────┴──────┴────────────┘ sqlite> SELECT * from manager; ┌────────────┬────────────┬───────┬───────┐ │ manager_id │ name │ phone │ email │ ├────────────┼────────────┼───────┼───────┤ │ 99 │ Clark Kent │ │ │ └────────────┴────────────┴───────┴───────┘

...but I'm still able to delete records that are linked with a foreign key.

This made no sense at first, but after witnessing your posted schema, I saw no conflict-handling clauses in it.

For instance you could add the clause "ON DELETE CASCADE" to indicate that if the principal record is removed (from the parent) then any child (which references it) should also be removed, or "ON CONFLICT RESTRICT" to indicate that if you attempt to remove a parent which is referenced by one or more children, the DB Engine should Stop you, by failing the operation, etc. I'm not sure which you would like to happen, but if FK's are correctly enabled (taking into account what Keith said w.r.t. transactions), and the conflict clause correctly stated, then you MUST get the correct actions.

You should read the Foreign Keys section carefully, make sure what you set and expect lines up, and if not, tell us again with full example schema and results.

Many thanks. I am new to foreign keys and I'm finding it a challenge to understand everything.

Sorry, a further question. I don't see RESTRICT as an option for ON CONFLICT.

The website lists ROLLBACK, ABORT, FAIL, IGNORE or REPLACE. I guess as I don't want the delete to occur if there is a constraint, I need ABORT.

You are looking at the ON CONFLICT for key violations for the "whole record".

There are several kinds of conflicts for which you can specify a resolution method. For example, there is the "whole record key conflict resolution" ON CONFLICT clause, there is an ON CONFLICT clause that can apply to a specific column, and there are ON CONFLICT clauses that apply to the FOREIGN KEY. There may be others.

Ryan, you should read https://sqlite.org/foreignkeys.html

There is no need for a conflict resolution method on foreign key constraints unless the default handling is insufficient. The default handling will prohibit removal of a parent that has children or creating a child for which the parent does not exist.

The default handling may be modified in various ways by specifying how to defer foreign key constraint checking or overriding the handling.

Thanks for pointing it out - I had read this many times before (since I use FK's extensively in my DBs), and upon examining it now again, I believe this is the quote that threw me in section 4.3:

The ON DELETE and ON UPDATE action associated with each foreign key in an SQLite database is one of "NO ACTION", "RESTRICT", "SET NULL", "SET DEFAULT" or "CASCADE". If an action is not explicitly specified, it defaults to "NO ACTION".

Reading it now again, armed with your previous response, I realize that what I always assumed, in that the default action is "NO ACTION", probably only holds when actually adding the ON DELETE or ON CASCADE clauses, and doesn't speak to what happens, as a default, when they are altogether avoided.

At least, that's what I now deduce from this conversation, haven't actually tested it yet - so feel free to point out if I still have it wrong.

W.r.t. the OP's problem - I suppose the only plausible thing left is that the pragma was called in a transaction.

The default NO ACTION (as in ON UPDATE NO ACTION or ON DELETE NO ACTION) means that NO ACTION is taken in order to mitigate the conflict which has arisen. If, as a result, the statement cannot be completed in its entirety and there is a non-deferred foreign key violation, then the statement is FAIL with an error.

RESTRICT means to immediately FAIL any statement that has a referential integrity violation notwithstanding that the foreign key constraint may be deferred (the deferral is ignored).

SET NULL means that when the parent is deleted, the child reference column is set to null thus allowing the parent to be deleted. If the child column cannot be set to null (it is declared NOT NULL for example) then the statement will FAIL.

SET DEFAULT is like SET NULL except that rather than NULL the default value for the column is used. If the child column cannot be set to the default (because that is a foreign key violation) then the statement will FAIL.

CASCADE means that if the parent column value is updated then the change will be cascaded to all the current children, and in the case of delete, that all the children will be deleted along with the parent. Unless, of course, this causes a conflict of some sort, in which case the statement will FAIL.

In actual fact, NO ACTION is actually a euphamism for the long and unwieldly (unless you are a COBOL programmer) DO NOT TAKE ANY ACTION TO ATTEMPT TO RESOLVE THE CONFLICT.

Thanks all for your helpful replies. I admit I am overwhelmed with information now.

Anyway, I have been able to confirm that foreign_keys is indeed on.

I also added a manager, added a hall with the new manager assigned, and when I tried to delete the manager, I got a fatal error - yey! I don't know what has changed, but all I need to do now is to capture the error programmatically and inform the user.

This is a useful detailed explanation. I don't use foreign keys much in my databases, so this is new to me.