Hi, everybody.
I’m trying to add a CHECK constraint to a table’s field using LIKE condition with wildcards.
Specifically, I need a VARCHAR field named “rut” to accept only values having the following pattern:
DDDDDDDD-[D or K]
(eight digits, followed by a ‘-’, and finally a digit or a ‘K’ letter).
I tried by going to TOOLS —> SQL and entering the following commands, but none of them works:
ALTER TABLE "Tabla1" ADD CONSTRAINT CHECK ("rut" LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]-[0-9K]')
ALTER TABLE "Tabla1" ADD CONSTRAINT CHECK ("rut" LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]-[0-9,K]')
ALTER TABLE "Tabla1" ADD CONSTRAINT CHECK ("rut" LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][-][0-9K]')
ALTER TABLE "Tabla1" ADD CONSTRAINT CHECK ("rut" LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][-][0-9,K]')
All these commands are being successfully executed, but when I try to input values like 20405768-K or 19993766-2, HSQL complains about an integrity constraint violation.
Thinking that the last 2 characters were the problem in my pattern, I changed it to
ALTER TABLE "Tabla1" ADD CONSTRAINT CHECK ("rut" LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')
and tried to input values like 20405768 or 19993766, but I got the same error.
Finally, I modified the constraint to
ALTER TABLE "Tabla1" ADD CONSTRAINT CHECK ("rut" LIKE 'a%')
but I can’t even enter values like ‘ace’ or ‘a’. What am I doing wrong?
Using: LibreOffice 5.1.1.2, compilation ID 1:5.1.1~rc2-0ubuntu1~trusty0
SQL engine: HSQLDB 2.3.2, with a ‘split database’ created following the instructions provided here
OS: Ubuntu 14.04 LTS (64 bits)
Any kind of help is really appreciated.
EDIT: I get the same behaviour with an embedded database using default HSQLDB engine. See the attached file. Run the ‘checks’ query to see the constraint imposed to “rut” field.