Sebastian Proske Sebastian Proske - 5 months ago 11
SQL Question

Firebird 2.5 - check contraint allowing only certain characters

I'm trying to create a column in firebird (2.5) in a way, that it:


  • cannot be NULL

  • must be exactly 16 characters

  • can only contain numbers and lowercase letters
    a
    -
    f



I could solve the first and second restriction.

ALTER TABLE TEST_TABLE ADD NEW_COLUMN CHAR(16) NOT NULL CHECK (CHAR_LENGTH(TRIM(VALUE)) = 16)


I would prefer to not use triggers for the third requirement, but a check constraint - is there any way to do this?

Answer

Can you use SQL regexes?

CHECK (value SIMILAR TO '[a-z0-9]{16}')

These were introduced in Firebird 2.5, and, conveniently for your purpose, must match the entire string. (That is, they're "anchored" at both ends of the search space.)