SpatiaLite logo

Recipe #8:
Understanding Constraints

2011 January 28

Previous Slide Table of Contents Next Slide

Understanding what constraints are is a very simple task following a conceptual approach.
But on the other side understanding why some SQL statement will actually fail raising a generic constraint failed exception isn't a so simple affair.

In order to let you understand better this paragraph is structured like a quiz:
  • you'll find first the questions.
  • corresponding answers are positioned at bottom.

Important notice:
in order to preserve your main sample database untouched, creating a different database for this session is strongly suggested.

GETTING STARTED

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');
Nothing too much complex: we simply have created two tables: And then we have inserted very few rows into these tables.

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
Just a simple check; and then you are now ready to start.

QUESTIONS


Q1: why this SQL statement will actually fail, raising a constraint failed exception ?
INSERT INTO children
    (first_name, last_name, mom_first_nm, mom_last_nm)
  VALUES ('Silvia', 'Bianchi', 'Maria', 'Rossi');

Q2: ... same question ...
INSERT INTO children
    (first_name, last_name, mom_first_nm, mom_last_nm, gender)
  VALUES ('Silvia', 'Bianchi', 'Maria', 'Rossi', 'f');

Q3: ... same question ...
INSERT INTO children
    (first_name, last_name, mom_first_nm, mom_last_nm, gender)
  VALUES ('Silvia', 'Bianchi', 'Giovanna', 'Rossi', 'F');

Q4: ... same question ...
INSERT INTO children
    (first_name, last_name, mom_first_nm, mom_last_nm, gender)
  VALUES ('Henry', 'Petit', 'Stephanie', 'Smith', 'M');

Q5: ... same question ...
INSERT INTO mothers (first_name, last_name, home_location)
  VALUES ('Pilar', 'Fernandez',
    ST_GeomFromText('POINT(4.7 45.6)'));

Q6: ... same 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: ... same question ...
INSERT INTO mothers (first_name, last_name)
  VALUES ('Pilar', 'Fernandez');

Q8: ... same question ...
INSERT INTO mothers (first_name, last_name, home_location)
  VALUES ('Pilar', 'Fernandez',
    ST_GeomFromText('POINT(4.7 45.6), 4326'));

Q9: ... same question ...
DELETE FROM mothers
WHERE last_name = 'Dupont';

Q10: ... same question ...
UPDATE mothers SET first_name = 'Marianne'
WHERE last_name = 'Dupont';

ANSWERS


A1: missing/undefined gender: so a NULL value is implicitly assumed.
But a NOT NULL constraint has been defined for the gender column.

A2: wrong gender value ('f'): SQLite text strings are case-sensitive.
The sex constraint can only validate 'M' or 'F' values: 'f' isn't an acceptable value.

A3: FOREIGN KEY failure.
No matching entry {'Rossi','Giovanna'} was found into the corresponding table [mothers].

A4: PRIMARY KEY failure.
An entry {'Petit','Henry'} is already stored into the children table.

A5: missing/undefined SRID: so a -1 value is implicitly assumed.
But a Geometry constraint has been defined for the corresponding column; an explicitly set 4326 SRID value is expected anyway for home_location geometries.

A6: wrong Geometry-type: only POINT-type Geometries will pass validation for the home_location column.

A7: missing/undefined home_location: so a NULL value is implicitly assumed.
But a NOT NULL constraint has been defined for the home_location column.

A8: malformed WKT expression: ST_GeomFromText() will return NULL (same as above).

A9: FOREIGN KEY failure: yes, the mothers table has no FOREIGN KEY.
But the children table instead has a corresponding FOREIGN KEY.
Deleting this entry from mothers will break referential integrity, so this one isn't an allowed operation.

A10: FOREIGN KEY failure: more or less, the same of before.
Modifying a PRIMARY KEY entry into the mothers table will break referential integrity, so this operation as well isn't admissible.

Lesson to learn #1:
An appropriate use of SQL constraints strongly helps to fully preserve your data in a well checked and absolutely consistent state.
Anyway, defining too much constraints may easily transform your database into a kind of inexpugnable fortress surrounded by trenches, pillboxes, barbed wire and minefields.
i.e. into something that surely nobody will define as user friendly.
Use sound common sense, and possibly avoid any excess.

Lesson to lean #2:
Each time the SQL-engine detects some constraint violation, a constraint failed exception will be immediately raised.
But this one is an absolutely generic error condition: so you have to use your experience and skilled knowledge in order to correctly understand (and possibly resolve) any possible glitch.

Previous Slide Table of Contents Next Slide

CC-BY-SA logo Author: Alessandro Furieri a.furieri@lqt.it
This work is licensed under the Attribution-ShareAlike 3.0 Unported (CC BY-SA 3.0) license.

GNU logo 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.