Note: these pages are no longer maintained

Never the less, much of the information is still relevant.
Beware, however, that some of the command syntax is from older versions, and thus may no longer work as expected.
Also: external links, from external sources, inside these pages may no longer function.

	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:

the mothers table contains a Geometry column
a relation exists between mothers and children: and consequently a FOREIGN KEY has been defined.
just a last point to be noted: in this example we'll use PRIMARY KEYs spanning across two columns:
but there isn't nothing odd in this ... it's a fully legitimate SQL option.

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

	Author: Alessandro Furieri a.furieri@lqt.it
	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.