Clé primaire et clé étrangère dans SQLite avec des exemples

Les sections ci-dessous expliquent SQLite les contraintes en détail, en commençant par la CLÉ PRIMAIRE et la CLÉ ÉTRANGÈRE qui définissent et relient les tables, et en couvrant les règles NOT NULL, DEFAULT, UNIQUE et CHECK qui valident les données dans chaque colonne.

SQLite contraintes

Les contraintes de colonne appliquent des règles aux valeurs insérées dans une colonne afin de valider les données. Ces contraintes sont définies lors de la création d'une table, dans la définition de la colonne. Elles garantissent la cohérence et l'exactitude des données stockées en rejetant les valeurs qui enfreignent les règles définies, telles que les doublons, les valeurs nulles ou les valeurs absentes d'une table liée.

SQLite Clé primaire

Toutes les valeurs d'une colonne de clé primaire doivent être uniques et non nulles. La clé primaire identifie de manière unique chaque ligne de la table.

La clé primaire peut s'appliquer à une seule colonne ou à une combinaison de colonnes. Dans ce dernier cas, la combinaison des valeurs des colonnes doit être unique pour toutes les lignes de la table.

syntaxe:

Il existe plusieurs façons de définir une clé primaire sur une table :

Dans la définition de la colonne elle-même :

ColumnName INTEGER NOT NULL PRIMARY KEY;

Comme définition distincte :

PRIMARY KEY(ColumnName);

Pour créer une combinaison de colonnes comme clé primaire :

PRIMARY KEY(ColumnName1, ColumnName2);

SQLite Contraintes NOT NULL, DEFAULT, UNIQUE et CHECK

Outre la clé primaire, SQLite fournit plusieurs contraintes de colonne qui valident les valeurs saisies dans une table. Les contraintes NOT NULL, DEFAULT, UNIQUE et CHECK sont chacune définies dans la définition de la colonne, et chacune d'elles applique une règle spécifique à la colonne. Type de données et valeurs.

Contrainte NOT NULL

Le SQLite La contrainte NOT NULL empêche une colonne d'avoir une valeur nulle :

ColumnName INTEGER  NOT NULL;

Contrainte PAR DÉFAUT

Avec SQLite Contrainte DEFAULT : si vous n'insérez aucune valeur dans une colonne, la valeur par défaut est insérée à la place.

Par exemple :

ColumnName INTEGER DEFAULT 0;

Si vous écrivez une instruction INSERT sans spécifier de valeur pour cette colonne, celle-ci aura la valeur 0.

Contrainte UNIQUE

Le SQLite La contrainte UNIQUE empêche les valeurs en double parmi toutes les valeurs de la colonne.

Par exemple :

EmployeeId INTEGER NOT NULL UNIQUE;

Cela garantit que la valeur « EmployeeId » est unique ; les valeurs dupliquées ne sont pas autorisées. Veuillez noter que cela s’applique uniquement aux valeurs de la colonne « EmployeeId ».

CONTRAINTE DE VÉRIFICATION

Le SQLite La contrainte CHECK définit une condition pour vérifier une valeur insérée. Si la valeur ne correspond pas à la condition, elle ne sera pas insérée.

Quantity INTEGER NOT NULL CHECK(Quantity > 10);

Vous ne pouvez pas saisir une valeur inférieure à 10 dans la colonne « Quantité ».

SQLite Clé étrangère

Le SQLite Une clé étrangère est une contrainte qui vérifie l'existence d'une valeur présente dans une table dans une autre table qui a une relation avec la première table où la clé étrangère est définie.

Lorsqu'on travaille avec plusieurs tables, il arrive que deux tables soient liées par une colonne commune. Pour garantir que la valeur insérée dans l'une existe également dans la colonne correspondante de l'autre table, il faut utiliser une contrainte de clé étrangère sur cette colonne commune.

Dans ce cas, lorsque vous tenterez d'insérer une valeur dans cette colonne, la clé étrangère garantira que la valeur insérée existe dans la colonne de la table référencée.

Notez que les contraintes de clé étrangère ne sont pas activées par défaut dans SQLiteVous devez d'abord les activer en exécutant la commande suivante :

PRAGMA foreign_keys = ON;

Des contraintes de clé étrangère ont été introduites dans SQLite à partir de la version 3.6.19.

Exemple de SQLite Clé étrangère

Supposons que nous ayons deux tables : Étudiants et Départements.

La table « Étudiants » contient la liste des étudiants, et la table « Départements » contient la liste des départements. Chaque étudiant est rattaché à un département ; autrement dit, chaque étudiant possède une colonne « departmentId ».

Nous allons maintenant voir comment la contrainte de clé étrangère peut être utile pour garantir que la valeur de l'identifiant du département dans la table Students doit exister dans la table Departments.

Ainsi, si nous créons une contrainte de clé étrangère sur le DepartmentId dans la table Students, chaque departmentId inséré doit être présent dans la table Departments.

CREATE TABLE [Departments] (
	[DepartmentId] INTEGER  NOT NULL PRIMARY KEY AUTOINCREMENT,
	[DepartmentName] NVARCHAR(50)  NULL
);
CREATE TABLE [Students] (
	[StudentId] INTEGER  PRIMARY KEY AUTOINCREMENT NOT NULL,
	[StudentName] NVARCHAR(50)  NULL,
	[DepartmentId] INTEGER  NOT NULL,
	[DateOfBirth] DATE  NULL,
	FOREIGN KEY(DepartmentId) REFERENCES Departments(DepartmentId)
);

Pour vérifier comment les contraintes de clé étrangère peuvent empêcher l'insertion d'un élément ou d'une valeur non définie dans une table liée à une autre table, nous allons examiner l'exemple suivant.

Dans cet exemple, la table Departments est liée à la table Students par une clé étrangère. Par conséquent, toute valeur departmentId insérée dans la table Students doit exister dans la table Departments. Si vous tentez d'insérer une valeur departmentId inexistante dans la table Departments, la contrainte de clé étrangère vous en empêchera.

Insérons deux départements, « Informatique » et « Arts », dans le tableau des départements avec les informations suivantes : requêtes INSERT:

INSERT INTO Departments VALUES(1, 'IT');
INSERT INTO Departments VALUES(2, 'Arts');

Ces deux instructions devraient insérer deux départements dans la table Departments. Vous pouvez vérifier que les deux valeurs ont bien été insérées en exécutant ensuite la requête « SELECT * FROM Departments ».

Essayez ensuite d'insérer un nouvel étudiant avec un departmentId qui n'existe pas dans la table Departments :

INSERT INTO Students(StudentName,DepartmentId) VALUES('John', 5);

La ligne ne sera pas insérée et vous recevrez une erreur indiquant : Échec de la contrainte de clé étrangère.

Différence entre clé primaire et clé étrangère dans SQLite

Les clés primaires et les clés étrangères contribuent toutes deux à garantir l'intégrité des données, mais leurs rôles diffèrent. Une clé primaire identifie les lignes au sein d'une même table, tandis qu'une clé étrangère établit un lien entre les lignes de deux tables liées. Le tableau ci-dessous récapitule les principales différences.

Dans l'exemple des étudiants et des départements, DepartmentId est la clé primaire de la table Departments et une clé étrangère dans la table Students, ce qui permet de lier chaque étudiant à un département valide.

SQLite Clé primaire composite

Une clé primaire composite est une clé primaire constituée de deux colonnes ou plus. Elle est utilisée lorsqu'aucune colonne n'est unique individuellement, mais que la combinaison des colonnes est unique pour chaque ligne. SQLite traite les valeurs combinées comme une seule clé.

Par exemple, un tableau d'inscription peut autoriser un même étudiant à suivre plusieurs cours et un même cours à suivre plusieurs étudiants, mais chaque paire étudiant-cours ne doit apparaître qu'une seule fois :

CREATE TABLE Enrollments (
	StudentId INTEGER NOT NULL,
	CourseId INTEGER NOT NULL,
	Grade TEXT,
	PRIMARY KEY (StudentId, CourseId)
);

Ici, ni StudentId ni CourseId ne sont uniques individuellement, mais la paire (StudentId, CourseId) l'est. Un même étudiant ne peut donc pas être inscrit deux fois au même cours. Notez les points suivants lorsque vous utilisez une clé composite :

• Utilisez une clé composite lorsqu'une seule colonne ne permet pas d'identifier une ligne de manière unique.
• Chaque colonne de la clé composite respecte les règles de la clé primaire, la valeur combinée doit donc être unique et non nulle.
• Une clé composite est écrite sous la forme d'une clause PRIMARY KEY distincte au niveau de la table, et non à l'intérieur d'une définition de colonne unique.

SQLite Actions sur les clés étrangères : ON DELETE et ON UPDATE

Une clé étrangère permet également de contrôler le comportement des lignes enfants lorsque la ligne parente à laquelle elles font référence est supprimée ou mise à jour. Ces actions référentielles sont définies par les clauses ON DELETE et ON UPDATE lors de la définition de la clé étrangère. SQLite prend en charge cinq actions :

• AUCUNE ACTION — l’action par défaut, qui génère une erreur si les lignes enfants font encore référence au parent.
• RESTREINDRE — empêche la suppression ou la mise à jour immédiate, avant toute autre modification.
• FIXER NULL — définit la colonne de clé étrangère enfant sur null.
• DÉFINIR PAR DEFAUT — définit la colonne de clé étrangère enfant sur sa valeur par défaut déclarée.
• CASCADE — applique la même modification aux lignes enfants, de sorte que la suppression d'un parent supprime également ses enfants.

L'exemple ci-dessous recrée la table Étudiants de sorte que la suppression d'un département entraîne automatiquement la suppression de ses étudiants, et que la mise à jour de l'identifiant d'un département mette à jour les étudiants correspondants :

CREATE TABLE Students (
	StudentId INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
	StudentName NVARCHAR(50) NULL,
	DepartmentId INTEGER NOT NULL,
	FOREIGN KEY(DepartmentId) REFERENCES Departments(DepartmentId)
		ON DELETE CASCADE
		ON UPDATE CASCADE
);

N'oubliez pas que les actions référentielles ne s'exécutent que lorsque la prise en charge des clés étrangères est activée ; par conséquent, exécutez PRAGMA foreign_keys = ON au début de chaque connexion. Sans cela, SQLite analyse les clauses ON DELETE et ON UPDATE mais ne les applique pas.

FAQ