Est-ce que SQLite ne préserve vraiment pas l'intégrité des données des contraintes de clé étrangère par défaut ?

Question

Versions plus récentes de SQLite support des contraintes de clés étrangères . Il est possible de définir

CREATE TABLE MASTER (_ID INTEGER PRIMARY KEY, ...);
CREATE TABLE SERVANT (_ID INTEGER PRIMARY KEY, MASTERID INTEGER, 
  FOREIGN KEY(MASTERID) REFERENCES MASTER(_ID);

Selon la documentation, par défaut, "NO ACTION" est utilisé pour ON DELETE et ON UPDATE. Mais contrairement à d'autres DBS, "NO ACTION" ne semble pas signifier que la suppression ou la mise à jour n'est pas effectuée. Cela semble signifier que rien n'est fait pour préserver l'intégrité, du moins d'après mes tests(*) et si je comprends l'objectif de l'opération. documentation droite :

Configurer "NO ACTION" signifie exactement cela : lorsqu'une clé parent est modifiée ou supprimée de la base de données, aucune action particulière n'est entreprise.

Ainsi,

INSERT INTO MASTER (_ID) VALUES (1);
INSERT INTO SERVANT (_ID, MASTERID) VALUES (1,1);
DELETE FROM MASTER;

me donne une table MASTER vide et une table SERVANT avec une clé étrangère qui ne mène nulle part.

Quelqu'un peut-il confirmer ce comportement et peut-être expliquer pourquoi il est mis en œuvre de cette façon ? Ou dois-je configurer quelque chose pour que le support des clés étrangères fonctionne ? Je suis nouveau dans le développement de SQLite, alors pardonnez-moi si cette question est stupide.

Editar: (*) mes tests étaient défectueux, cf. ma réponse ci-dessous .

Answer 1

Je vais essayer de donner une réponse moi-même :

Non, si la configuration est correcte, SQLite préserve l'intégrité des données dans cette situation. "NO ACTION" est utilisé par défaut et cela interdit la suppression ou la mise à jour d'une clé maîtresse s'il existe encore une clé de référence d'une table de référence (testé avec 3.7.x). Ma faute est que je n'étais pas conscient que PRAGMA foreign_keys = ON; doit être configuré pour chaque nouvelle connexion à la base de données.

Editar: Je pense que la documentation de SQLite est trompeur ici.

Answer 2

Vous avez raison. "NO ACTION" signifie que rien n'est fait pour préserver l'intégrité des contraintes de clé étrangère. Voir la documentation pour plus de détails sur les options que vous pouvez définir.

Il existe 4 autres options que vous pouvez définir dans ce scénario. RESTRICT, SET NULL, SET DEFAULT et CASCADE. Une brève description de ce qu'elles font :

RESTRICT - Une ligne de la table MASTER ne peut être supprimée que si elle n'est référencée par aucune ligne de la table SERVANT.

SET NULL - La suppression d'une ligne dans la table MASTER entraîne la mise à NULL de tous les FK de la table SERVANT.

SET DEFAULT - Similaire à set NULL sauf que le FK est mis à la valeur par défaut au lieu de NULL.

CASCADE - La suppression d'une ligne dans la table MASTER entraîne la suppression de toutes les lignes de la table SERVANT qui font référence à la ligne MASTER supprimée.

Pour modifier ces options, vous devrez modifier vos instructions de création pour spécifier les actions on update et on delete.

CREATE TABLE MASTER (
  _ID INTEGER PRIMARY KEY, 
  ...
);
CREATE TABLE SERVANT (
  _ID INTEGER PRIMARY KEY, 
  MASTERID INTEGER, 
  FOREIGN KEY(MASTERID) REFERENCES MASTER(_ID) ON UPDATE CASCADE ON DELETE SET NULL
);

modifier : N'oubliez pas de vous assurer que votre version de SQLite a été compilée avec le support des clés étrangères et que vous l'avez activé en spécifiant PRAGMA foreign_keys = ON;