Help with CHECK LIKE constraint using wildcards

preguntado 2016-03-22 03:21:55 +0100

Imagen Gravatar de roberto

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.

edit re-etiquetar marcar como ofensivo cerrar fusionar delete

Comments

Hola @roberto, no se si te has dado cuenta de que has hecho la pregunta en inglés en el foro en español.

Imagen Gravatar de m.a.riosv m.a.riosv ( 2016-03-23 00:37:41 +0100 )edit

Por otra parte, obtienes el mismo error actualizando el dato directamente desde TOOLS->SQL.

Imagen Gravatar de m.a.riosv m.a.riosv ( 2016-03-23 00:38:50 +0100 )edit