Cours MariaDB / Distinguer NULL, DEFAULT, NOT NULL et CHECK

NULL représente une information absente ou inconnue ; il diffère de zéro et d’une chaîne vide. DEFAULT fournit une valeur lorsqu’une colonne est omise, NOT NULL limite les valeurs absentes et CHECK vérifie une condition : combinez ces règles selon le besoin, car elles ne remplissent pas le même rôle.

Distinguer NULL, chaîne vide, zéro et omission

État de l’entrée Signification Exemple
NULL Valeur absente, inconnue ou pas encore déterminée Aucune note collectée
Chaîne vide '' Texte présent, de longueur zéro Note envoyée sans texte
0 Valeur numérique égale à zéro Aucune unité en stock
Colonne omise Colonne absente de la liste de INSERT Le stock reçoit sa valeur par défaut

Une application peut choisir de traiter une note non collectée et une note vide de la même façon. C’est une politique à définir : remplacer systématiquement NULL par une chaîne vide supprime cette distinction dans les données. L’exercice conserve le comportement ordinaire des chaînes et n’active pas de mode de compatibilité tel que EMPTY_STRING_IS_NULL.

Une comparaison ordinaire avec NULL produit généralement un résultat inconnu, représenté par NULL, plutôt que vrai ou faux. Pour rechercher une valeur absente, utilisez IS NULL et non = NULL. La référence sur les valeurs NULL décrit les comparaisons et les cas particuliers. L’absence d’une valeur est également une question distincte de l’unicité d’un identifiant.

Donner un rôle précis à chaque règle

Déclaration Rôle Limite
DEFAULT 0 Fournir zéro lorsque la colonne est omise ou que DEFAULT est demandé Ne remplace pas en général tout NULL fourni explicitement
NOT NULL Exiger une valeur non NULL N’interdit pas à lui seul une chaîne vide, zéro ou un nombre négatif
CHECK (stock >= 0) Refuser un stock pour lequel la condition est fausse Ne refuse pas à lui seul NULL si la colonne est nullable
NOT NULL et CHECK Exiger une valeur puis vérifier sa condition Ne remplace ni les messages du formulaire ni l’examen des anciennes données

Évitez de créer une information inconnue au moyen d’une valeur par défaut. Si le stock n’a pas été compté, enregistrer zéro peut le confondre avec une rupture de stock réelle. Cet exercice choisit explicitement la politique « stock initial d’un nouveau produit égal à zéro ». Une autre activité peut préférer NULL ou un état de vérification séparé.

La validation applicative permet de donner une explication utile à l’utilisateur. Les contraintes de la base protègent le point d’enregistrement, y compris lorsqu’un autre programme ou un fichier SQL écrit les données. Contrôler uniquement le formulaire ne garantit pas que tous les autres clients respecteront les règles.

Exécuter un exemple complet de valeurs par défaut

Connectez-vous à l’instance d’apprentissage avec course_user et la base shopcourse. Les cours sur la création de la base et le client et les fichiers SQL présentent les prérequis. Saisissez les commandes dans MariaDB, sans utiliser une connexion contenant des données de production :

SELECT VERSION() AS server_version, DATABASE() AS selected_database;
USE shopcourse;
SELECT DATABASE() AS selected_database;

Après USE, la base sélectionnée doit être shopcourse. Vérifiez que les noms validation_lab et nullable_check_lab ne désignent pas des tables à conserver. Enregistrez le bloc normal ci-dessous dans un fichier UTF-8 lesson11-null-default-constraints.sql, puis lancez-le avec SOURCE et son chemin local réel, ou saisissez les instructions dans l’ordre.

Le fichier sauvegarde le mode SQL et le réglage de contrôle des contraintes de cette connexion, puis active explicitement le mode strict et les CHECK. Gardez la même connexion ouverte jusqu’à leur restauration. Pour une ancienne installation, vérifiez la version et la prise en charge effective des contraintes : l’acceptation d’une syntaxe ne prouve pas à elle seule que la règle est appliquée.

USE shopcourse;
SET @saved_sql_mode = @@SESSION.sql_mode;
SET @saved_check_constraints = @@SESSION.check_constraint_checks;
SET SESSION sql_mode = 'STRICT_TRANS_TABLES';
SET SESSION check_constraint_checks = ON;
CREATE TABLE validation_lab (
  item_id INT PRIMARY KEY,
  item_name VARCHAR(100) NOT NULL,
  note VARCHAR(100) NULL DEFAULT NULL,
  stock INT NOT NULL DEFAULT 0,
  CONSTRAINT ck_validation_stock CHECK (stock >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO validation_lab (item_id, item_name, note) VALUES
  (1, 'Notebook', NULL),
  (2, 'Mug', '');
SELECT item_id, note IS NULL AS note_is_null,
       note = '' AS note_is_empty, stock
FROM validation_lab ORDER BY item_id;
CREATE TABLE nullable_check_lab (
  item_id INT PRIMARY KEY,
  stock INT NULL,
  CONSTRAINT ck_nullable_stock CHECK (stock >= 0)
) ENGINE=InnoDB;
INSERT INTO nullable_check_lab VALUES (1, NULL);
SELECT item_id, stock IS NULL AS stock_is_null FROM nullable_check_lab;

Notebook et Mug sont les noms fictifs d’un carnet et d’une tasse, conservés pour reproduire le fichier. La première requête doit donner :

item_id note_is_null note_is_empty stock
1 1 NULL 0
2 0 1 0

La première note est NULL : IS NULL donne vrai, soit 1, mais la comparaison avec une chaîne vide donne NULL. Pour la seconde note, la chaîne vide est une valeur connue : IS NULL donne 0 et note = '' donne 1. Les deux insertions omettent le stock, donc les deux lignes reçoivent zéro par défaut.

La dernière requête, sur nullable_check_lab, doit retourner item_id = 1 et stock_is_null = 1. Le CHECK ne refuse pas ce stock NULL, car NULL >= 0 ne donne pas faux : son résultat est inconnu. Si le stock doit être connu et positif ou nul, associez NOT NULL à la condition CHECK.

Tester séparément NOT NULL et CHECK

Après le fichier normal, dans la même connexion, exécutez cette instruction isolément. Elle fournit NULL pour le nom obligatoire :

INSERT INTO validation_lab (item_id, item_name) VALUES (3, NULL);

Une erreur indiquant que la colonne ne peut pas être NULL doit empêcher l’ajout. Vérifiez avec SELECT COUNT(*) FROM validation_lab; que le nombre de lignes reste égal à deux. Testez ensuite une entrée dont le nom existe mais dont le stock est négatif :

INSERT INTO validation_lab (item_id, item_name, stock) VALUES (3, 'Desk lamp', -1);

Cette fois, l’erreur doit identifier la contrainte ck_validation_stock, et le nombre de lignes doit encore être deux. L’identifiant 3 du premier essai n’a pas été enregistré : vous pouvez donc le réutiliser pour ce second échec. Les mots exacts du message dépendent de la version et de la langue de sortie ; repérez surtout la colonne ou la règle concernée.

Ne mélangez pas ces erreurs intentionnelles au milieu du fichier normal : elles peuvent en interrompre la lecture, notamment avec --abort-source-on-error. Examinez aussi l’état réel après un échec au lieu de supposer que toutes les opérations précédentes ont été annulées.

Le traitement d’un NULL explicite dépend de la déclaration, de la forme d’insertion, du mode SQL et parfois du type. AUTO_INCREMENT et TIMESTAMP ont des comportements particuliers dans certains cas. Les règles observées ici pour une note et un stock ordinaires ne doivent pas être généralisées à tous les types ; le cours sur les types temporels et leurs valeurs par défaut présente un de ces cas.

Contrôler les valeurs vides et l’application des règles

Une colonne textuelle NOT NULL peut encore contenir '', comme l’explique le cours sur CHAR, VARCHAR et TEXT. Pour refuser aussi un nom vide ou composé uniquement d’espaces ordinaires, vous pouvez concevoir une condition telle que CHECK (CHAR_LENGTH(TRIM(item_name)) > 0), en complément de NOT NULL. Le TRIM par défaut ne supprime pas nécessairement tous les espaces Unicode ni tous les caractères invisibles.

Examinez la déclaration enregistrée et les réglages de la connexion :

SHOW CREATE TABLE validation_lab;
SELECT @@SESSION.sql_mode, @@SESSION.check_constraint_checks;

Le nom ck_validation_stock facilite l’identification de la règle responsable d’un échec. Donnez aux contraintes des noms compréhensibles et vérifiez leur portée de nommage si vous devez prendre en charge plusieurs versions du serveur. La référence des contraintes MariaDB décrit CHECK et les contrôles disponibles.

Lorsque check_constraint_checks est désactivé, des écritures peuvent contourner les CHECK déclarés. Corrigez l’entrée ou la règle voulue au lieu de désactiver ce contrôle pour faire passer des données incorrectes. Le réactiver ne signifie pas non plus que toutes les anciennes lignes sont automatiquement corrigées. La documentation CREATE TABLE précise les déclarations de colonnes, valeurs par défaut et contraintes.

Restaurer les réglages et recommencer

Dans la connexion qui a sauvegardé les variables, restaurez les deux réglages :

SET SESSION check_constraint_checks = @saved_check_constraints;
SET SESSION sql_mode = @saved_sql_mode;

Si vous avez fermé cette connexion, les variables sauvegardées ne sont plus disponibles dans la nouvelle session. Consultez ses réglages courants au lieu de lui appliquer des variables vides. Les tables et les lignes restent enregistrées après la fermeture de la connexion.

Pour répéter l’exercice, contrôlez la base avec SELECT DATABASE();, puis examinez la définition et les données de chacune des deux tables, par exemple avec SHOW CREATE TABLE shopcourse.validation_lab; et l’équivalent pour nullable_check_lab. Sauvegardez les données à conserver. Seulement après avoir confirmé ces deux cibles d’apprentissage, exécutez :

DROP TABLE shopcourse.validation_lab, shopcourse.nullable_check_lab;

Cette commande supprime les deux tables indiquées et toutes leurs lignes ; un ROLLBACK ordinaire ne les restaure pas. La table products du premier exercice de produits reste hors de cette réinitialisation. Relancez le fichier normal pour retrouver les deux notes différentes, leurs stocks par défaut et la ligne nullable acceptée par CHECK.