SQLite - Check Contraints

Prelude:

Rule 1. I do not trust the backend.
Rule 2. I do not trust the frontend.
Rule 3. I and only I decide what should I store or not.

When managing a database you want to remember the three rules above. As we do not know which silly magic is used to fill the data in our database we need to ensure the integrity of our data. defining a data type (text, integer, date) is not secure enough. So most database manager (MySql, SQLite, MariaDB) use CHECK Constraints. It is a simple process to verify that the inputs fits the requirements of your app before inserting any data in your tables.

Important: CHECK(...) behave like if constraints which means you can perfom any operations within the parenthesis. The list of operations you can use in your SQLite queries can be found here

There is two ways you can perfom constraints, Column scope and Table scope.

Column scope will ensure that your input is correct in a specific field. e.g: Age is a number

Table scope will ensure that two or more field are satisfying each other. e.g: parent_age > child_age

Integer Constraints

-- Columns Constraint
---------------------
> Sample: CHECK(typeof(ColumnName) = 'integer')

-- Practical Example
DROP TABLE IF EXISTS example;
CREATE TABLE example (
    age integer NOT NULL CHECK(typeof(age) = 'integer')
);
-- Tests
INSERT INTO example (age) VALUES (0.5);
INSERT INTO example (age) VALUES ("1");
INSERT INTO example (age) VALUES ("One");
--> OUTPUT : constraint failed
INSERT INTO example (age) VALUES(1);
--> OUTPUT : query executed successfully. Took 0ms, 1 rows affected

Note: Integer in SQLite are also used for boolean (0, 1) to represents false and true values

-- Boolean Checks
-----------------
> Sample: CHECK(ColumnName IN (0,1)) -- Accept Range between 0 and 1

-- Practical Example
DROP TABLE IF EXISTS example;
CREATE TABLE example (
    isHuman integer NOT NULL CHECK(isHuman IN (0,1))
);

-- Tests
INSERT INTO example (isHuman) VALUES (0.5);
INSERT INTO example (isHuman) VALUES ("-1");
INSERT INTO example (isHuman) VALUES ("true"); -- !! Workaround with case below !! --
--> OUTPUT : constraint failed

INSERT INTO example (isHuman) VALUES(0);
INSERT INTO example (isHuman) VALUES(1);
INSERT INTO example (isHuman) VALUES (true); -- true will be converted to 1.
--> OUTPUT : query executed successfully. Took 0ms, 3 rows affected

If your frontend is using true or false value as default a workaround exist with CASE

Important: this only concern INSERT or SELECT statement and has nothing todo with CHECK

-- INSERTION
------------
INSERT INTO example (isHuman) values (
CASE 
	WHEN `${YOUR_BACKEND_VALUE}` = 'true' THEN 1 ELSE 0
END
);

-- Selection
------------
SELECT 
CASE 
    WHEN isHuman = 1 THEN 'true' ELSE 'false'
END isHuman
FROM example
-- Important note: returned value will be typeOf(String(Text)) therefore you will need to convert it to Boolean() to perfom logicial operations.

Text Constraints

In practice Text Constraints can handle any cases you want. Note that we do not need to use the typeOf method because everything you insert in a text field is casted into string values.

-- Length Checks
----------------
> CHECK (length(ColumnName) <= 5) -- Atleast five characters OR less
> CHECK (length(ColumnName) >= 5) -- Atleast five characters OR more
> CHECK (length(ColumnName) BETWEEN 1 AND 200) -- Between 1 to 200 characters

-- Word Checks
--------------------
-- Restricted input e.g Gender
------------------------------
-- Specific case sensitive
> CHECK(ColumnName IN ("Male", "Female"))
-- Specific case insensitive
> CHECK(LOWER(ColumnName) IN ("male", "female")) 
-- First Letter Uppercase
> CHECK(substr(ColumnName,1,1) = UPPER(substr(ColumnName,1,1))) 
-- Note: We used substr function to extract the first letter and compare default value to the Uppercase version. 
-- e.g: (a)bcedf == (A)bcdef ? => false

-- Link Checks
--------------
> Sample: CHECK(ColumnName LIKE "http://%" OR LIKE "https://%") -- Can be improved
-- Email Checks
---------------
> Sample: CHECK(ColumnName LIKE "%@%") -- Can be improved 
-- Note: % is used to check if there is anything before or after

-- Practical Example
DROP TABLE IF EXISTS example;
CREATE TABLE example
(
	name text DEFAULT NULL CHECK (length(name) > 2 AND substr(name,1,1) = UPPER(substr(name,1,1))),
    gender text NOT NULL DEFAULT "Male" CHECK(gender IN ("Male", "Female")),
    email text NOT NULL CHECK(email LIKE "%@%")
);

INSERT INTO example (name, gender, email) VALUES 
("phil", "Male", "[email protected]"), -- Name (Case Sensitive)
("Phil", "seahorse", "[email protected]"), -- Incorrect Gender
("Phil", "Male", "phil#codi.tech"); -- Incorrect Email
-- OUTPUT: CHECK constraint failed: example     

INSERT INTO example (name, gender, email) VALUES ("Phil", "Male", "[email protected]");
-- OUTPUT: query executed successfully. Took 0ms, 1 rows affected

Date Constraints

SQLite does not have a plain date type format we use text or blob therefore SQLite provide us with DATE(...) functions. We can also use them to check our inputs. The default date format in SQLite is (YYYY-MM-DD);

-- Columns Constraint
---------------------

-- Date: Check if date is in the correct format by enforcing the correct format for the input by using DATE(...)
> Sample: CHECK(DATE(ColumnName) NOT NULL AND DATE(ColumnName) = ColumnName)

-- Practical Example
DROP TABLE IF EXISTS example;
CREATE TABLE example
(
	creation_date text DEFAULT NULL CHECK(DATE(creation_date) NOT NULL AND DATE(creation_date) = creation_date)
);
-- Tests
INSERT INTO example (creation_date) VALUES 
("abcdef"), -- Not a date
("12-25-2019"), -- Incorrect date format (MM-DD-YYYY)
("2019-25-12"), -- Incorrect Month
("2019-12-32"); -- Incorrect Day
-- All the queries above will result to: 
--> OUTPUT :  CHECK constraint failed: example

-- In fact the only viable input is:
INSERT INTO example (creation_date) VALUES ("2019-12-25");
--> OUTPUT : query executed successfully. Took 0ms, 1 rows affected

Notice SQLite will not handle leap year by himself and there's no solution for that.

In the case of start_date and end_date it is also important to check if the end_date is superior to the start_date. Take a look at the Table Constraints!.

Table Constraints

-- Tables Constraint (Simple)
---------------------
> Sample: CHECK (start_date (operator) end_date),

-- Practical Example
DROP TABLE IF EXISTS example;
CREATE TABLE example
(
	start_date text DEFAULT NULL CHECK(DATE(start_date) NOT NULL AND DATE(start_date) = start_date),
    end_date text DEFAULT NULL CHECK(DATE(end_date) NOT NULL AND DATE(end_date) = end_date),
	CHECK (end_date > start_date) -- < Notice the change? We have placed the check in the scope of the table.
);

-- Tests
INSERT INTO example (start_date, end_date) VALUES ("2019-12-25", "2018-12-25");
--> OUTPUT :  CHECK constraint failed: example
INSERT INTO example (start_date, end_date)  VALUES ("2019-12-25", "2020-12-25");
--> OUTPUT : query executed successfully. Took 0ms, 1 rows affected

In the case above we simply checked if the end_date is after the start_date. Nothing impressive...

What if we have more complex operations? Well... No problem! As we declared in the prelude Check(...) can handle any kind of logic let's implement some with the integer example.

DROP TABLE IF EXISTS example;
CREATE TABLE example (
    parent_age integer NOT NULL CHECK(typeof(parent_age) = 'integer'),
    child_age integer NOT NULL CHECK(typeof(child_age) = 'integer'),
    -- We want to make sure the age of both parent and child is higher than 0
    -- and the gap between the parent and the child is atleast 15 years
    CHECK((parent_age > 0 AND child_age > 0) -- Do not allow negative value
    AND (parent_age - child_age) >= 15) -- Do not allow lower than 15 years gap
);

INSERT INTO example (parent_age, child_age) VALUES(50, 40); -- Basic math: (50 - 40) = 10 => is 10 >= 15 ? No
--> OUTPUT : constraint failed
INSERT INTO example (parent_age, child_age) VALUES(50, 35); -- Basic math: (50 - 35) = 15 => is 15 >= 15 ? Yes
--> OUTPUT : query executed successfully. Took 0ms, 1 rows affected

Done!

callmephil/README.md