Regular Expression Integrity Constraints in SQLite3

Question

In a SQLite3 CREATE TABLE statement, can I use a regular expression to enforce a particular constraint? Specifically, I am trying to require that an inputted URL is valid. I know there are other ways of doing this besides using regular expressions, but because of the structure of my project, this is the method I need.

Answer 1

You can use REGEXP with a CHECK() constraint.

First, I would run a check on the column that you want to validate to ensure it will pass the constraint:

select * from staff where emp_ref not regexp '[A]?[0-9]{6}';

This should output any records that won't pass your proposed constraint (check in SQLite terminology). This is useful as it will highlight any potential issues with your data and/or your regex.

Unfortunately, due to a limitation in the architecture of SQLite, you can't add a check to a previously created column. To enable a check on a row of a previously created table, you have to create a new column with a different name:

alter table staff
add column employee_ref char(10)
check(employee_ref regexp '[A]?[0-9]{6}');

You can then delete the old column:

alter table staff drop column <colname>;