SQLite – Limit Characters in Different Parts of a String with Check Constraints

check-constraintssqlite

I have to make sure that any inserted Email has the format: "X@Y.Z"

X and Y may only contain letters and numbers and Z can only have Letters.

I tried something like this, but i cant figure out how to limit the Characters for X,Y and Z separately.

Create Table User(
Mail varchar check(Mail NOT GLOB '*[^A-Za-z0-9@.]*' AND Mail LIKE '%_@%_._%'));

Best Answer

So i figured out how to do exactly what i wanted to do. I used substrings and limited the characters for each individual part of the EMail.

CREATE TABLE User(
EMail varchar NOT NULL COLLATE NOCASE check( (substr(EMail, INSTR(EMail,'.')+1) NOT GLOB '*[^A-Za-z]*') 
AND (substr(EMail,1, INSTR(EMail,'@')-1) NOT GLOB '*[^A-Za-z0-9]*')
AND (substr(EMail, INSTR(EMail,'@')+1, ((INSTR(EMail,'.')-1) - (INSTR(EMail,'@')+1))+1 ) NOT GLOB '*[^A-Za-z0-9]*') 
AND EMAIL LIKE '%_@%_.%_')

Related Solutions

SQLite – Create Table with Check Constraint from Another Table

Looking up a value from another table would require a subquery, but the documentation says:

The expression of a CHECK constraint may not contain a subquery.

There is no such restriction on a trigger's WHERE clause:

CREATE TRIGGER employee_salary_check
BEFORE INSERT ON employee
WHEN NEW.salary < (SELECT min FROM position WHERE id_position = NEW.id_position)
  OR NEW.salary > (SELECT max FROM position WHERE id_position = NEW.id_position)
BEGIN
  SELECT RAISE(FAIL, "invalid salary");
END;

SQLite Default Timeout Value in Connection String – Explanation

I found this out by looking at the source code: The timeout value of a command is initialized to DefaultTimeout and passed to the internal SQLite3.Prepare function.

This is used to control a loop that automatically retries statements that fail with a Locked or Busy error. When the timeout has expired (as measured by Environment.TickCount), then an exception is thrown with whatever the most recent error code was. Otherwise, it sleeps for a random amount of time (between 1 and 150 ms) and tries again.

Best Answer

Related Solutions

SQLite – Create Table with Check Constraint from Another Table

SQLite Default Timeout Value in Connection String – Explanation

Related Question