Recette #8:
Comprendre les contraintes

Février 2011


Précédent

Table des matières

Suivant


Comprendre les constraints (contraintes)  du point de vue conceptuel, est relativement simple.
Mais d'un autre côté, comprendre pourquoi des requêtes SQL échouent en aboutissant sur une exception constraint failed n'est pas une simple affaire.

Afin de vous permettre de mieux comprendre, cette section est réalisée sous forme de quiz:

  • dans un premier temps vous trouverez les questions.

  • les réponses correspondantes sont à la fin.


Important:
Afin de préserver votre BDD principale, je vous suggère d'effectuer une sauvegarde avant de continuer.

Commençons

CREATE TABLE mothers (
  first_name TEXT NOT NULL,
  last_name TEXT NOT NULL,
  CONSTRAINT pk_mothers
    PRIMARY KEY (last_name, first_name));

SELECT AddGeometryColumn('mothers', 'home_location',
  4326, 'POINT', 'XY', 1);

CREATE TABLE children (
  first_name TEXT NOT NULL,
  last_name TEXT NOT NULL,
  mom_first_nm TEXT NOT NULL,
  mom_last_nm TEXT NOT NULL,
  gender TEXT NOT NULL
    CONSTRAINT sex CHECK (
      gender IN ('M', 'F')),
  CONSTRAINT pk_childs
    PRIMARY KEY (last_name, first_name),
  CONSTRAINT fk_childs
    FOREIGN KEY (mom_last_nm, mom_first_nm)
      REFERENCES mothers (last_name, first_name));

INSERT INTO mothers (first_name, last_name, home_location)
  VALUES ('Stephanie', 'Smith',
    ST_GeomFromText('POINT(0.8 52.1)', 4326));

INSERT INTO mothers (first_name, last_name, home_location)
  VALUES ('Antoinette', 'Dupont',
    ST_GeomFromText('POINT(4.7 45.6)', 4326));

INSERT INTO mothers (first_name, last_name, home_location)
  VALUES ('Maria', 'Rossi',
    ST_GeomFromText('POINT(11.2 43.2)', 4326));

INSERT INTO children
    (first_name, last_name, mom_first_nm, mom_last_nm, gender)
  VALUES ('George', 'Brown', 'Stephanie', 'Smith', 'M');

INSERT INTO children
    (first_name, last_name, mom_first_nm, mom_last_nm, gender)
  VALUES ('Janet', 'Brown', 'Stephanie', 'Smith', 'F');

INSERT INTO children
    (first_name, last_name, mom_first_nm, mom_last_nm, gender)
  VALUES ('Chantal', 'Petit', 'Antoinette', 'Dupont', 'F');

INSERT INTO children
    (first_name, last_name, mom_first_nm, mom_last_nm, gender)
  VALUES ('Henry', 'Petit', 'Antoinette', 'Dupont', 'M');

INSERT INTO children
    (first_name, last_name, mom_first_nm, mom_last_nm, gender)
  VALUES ('Luigi', 'Bianchi', 'Maria', 'Rossi', 'M');

Rien de très complexe: on a simplement créé deux tables:

Enfin, nous avons insérés quelques données à cette table.

SELECT m.last_name AS MomLastName,
  m.first_name AS MomFirstName,
  ST_X(m.home_location) AS HomeLongitude,
  ST_Y(m.home_location) AS HomeLatitude,
  c.last_name AS ChildLastName,
  c.first_name AS ChildFirstName,
  c.gender AS ChildGender
FROM mothers AS m
JOIN children AS c ON (
  m.first_name = c.mom_first_nm
  AND m.last_name = c.mom_last_nm);


MomLastName

MomFirstName

HomeLongitude

HomeLatitude

ChildLastName

ChildFirstName

ChildGender

Smith

Stephanie

0.8

52.1

Brown

George

M

Smith

Stephanie

0.8

52.1

Brown

Janet

F

Dupont

Antoinette

4.7

45.6

Petit

Chantal

F

Dupont

Antoinette

4.7

45.6

Petit

Henry

M

Rossi

Maria

11.2

43.2

Bianchi

Luigi

M

Un coup d'oeil et nous sommes prêts a commencer.

QUESTIONS


Q1: pourquoi cette requête va elle échouer et aboutir à  une exception constraint failed  ?

INSERT INTO children
    (first_name, last_name, mom_first_nm, mom_last_nm)
  VALUES ('Silvia', 'Bianchi', 'Maria', 'Rossi');


Q2: ... même question ...

INSERT INTO children
    (first_name, last_name, mom_first_nm, mom_last_nm, gender)
  VALUES ('Silvia', 'Bianchi', 'Maria', 'Rossi', 'f');


Q3: ... même question ...

INSERT INTO children
    (first_name, last_name, mom_first_nm, mom_last_nm, gender)
  VALUES ('Silvia', 'Bianchi', 'Giovanna', 'Rossi', 'F');


Q4: ... même question ...

INSERT INTO children
    (first_name, last_name, mom_first_nm, mom_last_nm, gender)
  VALUES ('Henry', 'Petit', 'Stephanie', 'Smith', 'M');


Q5: ... même question ...

INSERT INTO mothers (first_name, last_name, home_location)
  VALUES ('Pilar', 'Fernandez',
    ST_GeomFromText('POINT(4.7 45.6)'));


Q6: ... même question ...

INSERT INTO mothers (first_name, last_name, home_location)
  VALUES ('Pilar', 'Fernandez',
    ST_GeomFromText('MULTIPOINT(4.7 45.6, 4.75 45.32)', 4326));


Q7: ... même question ...

INSERT INTO mothers (first_name, last_name)
  VALUES ('Pilar', 'Fernandez');


Q8: ... même question ...

INSERT INTO mothers (first_name, last_name, home_location)
  VALUES ('Pilar', 'Fernandez',
    ST_GeomFromText('POINT(4.7 45.6), 4326'));


Q9: ... même question ...

DELETE FROM mothers
WHERE last_name = 'Dupont';


Q10: ... même question ...

UPDATE mothers SET first_name = 'Marianne'
WHERE last_name = 'Dupont';

Réponses


A1:  gender non défini: une valeur NULL est alors implicitement envoyée.
mais une contrainte NOT NULL a été définie pour cette colonne.

A2: la valeur ('f') n'est pas une valeur valide: SQLite est sensible à la casse (case-sensitive) pour les donnée de type textes.
La contrainte sex restreint le choix des valeurs à 'M' et 'F' : 'f' n'est pas accepté.

A3: FOREIGN KEY manquante.
Pas d'entrées correspondantes {'Rossi','Giovanna'} dans la table mothers.

A4: PRIMARY KEY manquante.
l'entrée {'Petit','Henry'} est déjà présente dans la table children.

A5: SRID non défini: la valeur -1 est alors implicitement envoyée.
mais la contrainte Géométrique définie pour cette colonne n'autorise que le SRID 4326.

A6: Mauvais type géométrique: seuls les POINT seront autorisés pour la colonne home_location.

A7: home_location non défini: une valeur NULL est envoyée.
mais une contrainte NOT NULL a été définie pour cette colonne.

A8: expression WKT non valide: ST_GeomFromText() retournera NULL.

A9: FOREIGN KEY manquante: oui, la table mothers n'a pas de FOREIGN KEY.
en revanche la table children pen possède une.
Supprimer une entrée dans la table mothers va casser l'intégrité référentielle, donc cette opération n'est pas autorisée.

A10: FOREIGN KEY manquante: plus ou moins la même chose que précédemment.
Modifier une donnée de type PRIMARY KEY dans la table mothers va casser l'intégrité référentielle, donc cette opération n'est pas autorisée.


Leçon #1:
Un usage approprié des contraintes en SQL aide grandement à préserver l'intégrité des données.
Cependant, définir trop de contraintes transforme rapidement votre BDD en une sorte de forteresse entourée de fils barbelés, inaccessible.
i.e. d'une complexité extrême.  Évitons les excès!

Leçon #2:
Chaque fois que le moteur SQL détecte une violation de contrainte, il affiche une exception constraint failed.
Cette exception est entièrement générique: vous devez utilisez vos connaissances et votre cerveau pour détecter d'où viens l’erreur

Précédent

Table des matières

Suivant


Author: Alessandro Furieri a.furieri@lqt.it
Traduced from English By RIVIERE Romain

This work is licensed under the Attribution-ShareAlike 3.0 Unported (CC BY-SA 3.0) license.

Permission is granted to copy, distribute and/or modify this document under the terms of the
GNU Free Documentation License, Version 1.3 or any later version published by the Free Software Foundation;
with no Invariant Sections, no Front-Cover Texts, and no Back-Cover Texts.