3

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.

1 Answer 1

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.