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

 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';

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.

 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.