SQLite Forum

Foreign key that is never enforced?
Login

Foreign key that is never enforced?

(1) By example-user on 2021-05-06 17:29:51 [link] [source]

Hello,

Is it possible to have a foreign key that is never enforced when writing to the database?

I'd like the foreign key to exist as many GUI tools read the schema and allow jumping to the related rows.

I am representing a JSON graph as SQL tables/rows, and some relations are allowed to be violated.

As an example, there are two objects, customer, subscription. A customer can be deleted, but a subscription cannot be deleted.

A FK exists on subscription: FOREIGN KEY(customer) REFERENCES Customer(id).

When the customer is deleted I do not want any error to be thrown.

(2) By Keith Medcalf (kmedcalf) on 2021-05-06 23:21:34 in reply to 1 [link] [source]

This is the purpose of the clause ON DELETE SET NUL

Of course, if you do not want to set the referent to NULL and instead let it be invalid, then what you are discussing is not a foreign key since the very definition of a foreign key requires the existence of a parent for each child except when the child is NULL.

(3) By Ryan Smith (cuz) on 2021-05-06 23:26:01 in reply to 1 [link] [source]

Simply declare the FK with NO ACTION constraints?

CREATE TABLE t(
  id INTEGER PRIMARY KEY,
  parent INT REFERENCES p(id) ON DELETE NO ACTION ON UPDATE NO ACTION,
  ...
  etc.
);
See: CREATE TABLE - FK Clause
and: Foreign Keys Actions

(4) By Keith Medcalf (kmedcalf) on 2021-05-06 23:45:39 in reply to 3 [source]

NO ACTION just means to take NO ACTION to make the FK constraint valid. This does not prevent the deletion of the parent from failing if there are children and foreign key enforcement is enabled.

(5) By anonymous on 2021-05-07 08:09:56 in reply to 1 [link] [source]

There’s nothing to do, since FKs are OFF by default...
Just declare your FKs, and they will NOT be enforced.
Unless the app you use explicitly turns them ON, on the connection.
The fact that the FK pragma is NOT PERSISTENT in the DB file is very unfortunate,
but exactly what you want in your case.