Usando expresiones regulares en la restricción CHECK

2 comentarios

Las expresiones regulares son muy útiles cuando escribimos un SELECT para filtrar el contenido de las tablas y así obtener los datos que nos interesan. Algo que poca gente sabe es que también pueden ser usadas en las restricciones CHECK, y que si las usamos allí disminuiremos la probabilidad de que entre “basura” en nuestras tablas.

Caracteres que pueden ser usados en las expresiones regulares:

[ ]  ( )  { } |  ^  -  +  *  % _ ?

Clases de caracteres que pueden ser usadas en las expresiones regulares

[:ALPHA:]
[:DIGIT:]
[:ALNUM:]
[:UPPER:]
[:LOWER:]
[:SPACE:]
[:WHITESPACE:]

Entonces, si nuestra columna es CHAR o VARCHAR podríamos escribir restricciones CHECK como las siguientes:

Ejemplo 1.

Listado 1.

ALTER TABLE 
   SUSTANTIVOS
ADD CONSTRAINT 
   CHK_SUSTANTIVOS CHECK (
      SUS_NOMBRE SIMILAR TO '_ASO'
   )

Que aceptará a ‘CASO’, a ‘VASO’, a ‘PASO’, a ‘RASO’, pero no a ‘CASOS’ ni a ‘AFRICA’. ¿Por qué? porque el primer carácter puede ser cualquiera pero los 3 siguientes deben ser sí o sí ‘ASO’. Y deben usarse 4 caracteres, ni uno más.

Ejemplo 2.

Listado 2.

 
ALTER TABLE 
   SUSTANTIVOS 
ADD CONSTRAINT 
   CHK_SUSTANTIVOS CHECK (
      SUS_NOMBRE SIMILAR TO '[[:ALPHA:]]*' )

Ahora le estamos ordenando que acepte solamente caracteres alfabéticos, si intentamos introducir números o cualquier carácter que no sea una letra, los rechazará.

Ejemplo 3.

Listado 3.

 
ALTER TABLE 
   SUSTANTIVOS 
ADD CONSTRAINT 
   CHK_SUSTANTIVOS CHECK (
      SUS_NOMBRE SIMILAR TO '[[:DIGIT:]]*' )

Y ahora le estamos diciendo que acepte solamente dígitos. Cualquier otro carácter será rechazado.

Conclusión:

La restricción CHECK es muy útil para impedir que entre “basura” en nuestra Base de Datos. Como sabes, se le llama “basura” a cualquier carácter, palabra o dato que está pero que no debería estar.

Las expresiones regulares son muy usadas en los SELECT para filtrar las filas que nos interesan, y es bueno saber que también pueden ser usadas en las restricciones CHECK. De esta manera son una herramienta más que tenemos para asegurarnos que el contenido de nuestras tablas sea correcto y consistente.

Si aún no sabes usar las expresiones regulares, en los siguientes enlaces hay artículos que te mostrarán como se usan.

Artículos relacionados:

Los predicados de comparación

Usando SIMILAR TO

Validando un e-mail

El índice del blog Firebird21

El foro del blog Firebird21

Indexando una columna computada

Deja un comentario

Como sabes, las columnas computadas son muy útiles para ver su contenido en los SELECTs y también cuando las usamos en la cláusula WHERE (de un UPDATE, de un DELETE, o de un SELECT) o en la cláusula ORDER BY.

Ya las habíamos visto en varios artículos anteriormente, ahora veremos una de sus características más interesantes: pueden ser indexadas.

Las primeras versiones del Firebird no lo permitían, pero a partir de la versión 2.0 ya tenemos esa posibilidad.

Por ejemplo, la tabla de SUCURSALES tiene estos datos:

INDICE1

Captura 1. Si haces clic en la imagen la verás más grande

Como podemos ver, algunos nombres están en mayúsculas y algunos nombres están en minúsculas. Podríamos crear el siguiente índice:


CREATE INDEX IDX_SUCURSALES1 ON SUCURSALES COMPUTED BY (UPPER(SUC_NOMBRE));

COMMIT;

Y luego usarlo en comandos como los siguientes:


SELECT * FROM SUCURSALES ORDER BY UPPER(SUC_NOMBRE)

INDICE2

Captura 2. Si haces clic en la imagen la verás más grande

Aquí, los nombres de las sucursales aparecen ordenados alfabéticamente y al mirar el PLAN vemos que efectivamente se usó el índice recientemente creado.


SELECT * FROM SUCURSALES WHERE UPPER(SUC_NOMBRE) STARTING WITH 'BOG'

INDICE3

Captura 3. Si haces clic en la imagen la verás más grande

Aquí le pedimos que nos muestre los nombres de todas las sucursales que empiezan con las letras ‘BOG’ y vemos que también usó el índice computado que habíamos creado anteriormente.


DELETE FROM SUCURSALES WHERE UPPER(SUC_NOMBRE) = 'MIAMI - ESTADOS UNIDOS'

INDICE4

También en este caso se usó el índice de la columna computada, como podemos comprobarlo mirando el PLAN.

Otros ejemplos:


CREATE INDEX IDX_MOVIMCAB1 ON MOVIMCAB COMPUTED BY (EXTRACT(YEAR FROM MVC_FECHAX))

CREATE INDEX IDX_MOVIMCAB2 ON MOVIMCAB COMPUTED BY (EXTRACT(YEAR FROM MVC_FECHAX) || MVC_NRODOC)

CREATE DESCENDING INDEX IDX_MOVIMCAB3 ON MOVIMCAB COMPUTED BY (EXTRACT(YEAR FROM MVC_FECHAX) || MVC_NRODOC)

El índice IDX_MOVIMCAB1 de la tabla MOVIMCAB (cabecera de movimientos) la indexa según el año del movimiento.

El índice IDX_MOVIMCAB2 de la tabla MOVIMCAB (cabecera de movimientos) la indexa según el año del movimiento y el número del documento.

El índice IDX_MOVIMCAB3 de la tabla MOVIMCAB (cabecera de movimientos) es muy parecido al índice IDX_MOVIMCAB2 pero ahora el índice es descendente (es decir, de mayor a menor).

Conclusión:

Tener columnas computadas es algo muy bueno porque nos facilita la vida. Poder indexar a esas columnas computadas es algo más bueno aún.

Como hemos visto en los ejemplos anteriores, el Firebird usará a los índices de las columnas computadas cada vez que los necesita.

Esos índices pueden involucrar a una sola columna o a varias columnas (como en los dos últimos ejemplos donde IDX_MOVIMCAB2 y IDX_MOVIMCAB3 involucran a las columnas MVC_FECHAX y MVC_NRODOC).

Artículos relacionados:

Columnas computadas

Un truco para encontrar valores que pueden estar en varias columnas

Usando un SELECT en una columna computada

Utilizando columnas computadas

Algunos ejemplos de uso de las columnas computadas

Usando un PLAN

Algo más sobre PLAN

Entendiendo el contenido de un PLAN

El índice del blog Firebird21

El foro del blog Firebird21

Conceptos sobre las Foreign Keys

2 comentarios

Si hasta ahora no has usado a las Foreign Keys podrías pensar que son innecesarias o que podrías muy bien manejarte sin ellas. Bien, es posible vivir sin usarlas, pero si las usas ganarás muchísimo en confiabilidad y en seguridad de tus datos.

Una Foreign Key sirve para relacionar a dos tablas entre sí.

Una de las tablas es la tabla “padre” y la otra tabla es la tabla “hija”.

En la tabla “hija” solamente pueden introducirse valores que ya existan en la tabla “padre”.

El Firebird no te permitirá que en la tabla “hija” coloques un valor que no existe en la tabla “padre”. Y eso es muy bueno porque evita que tengas filas “huérfanas”. Se llama “fila huérfana” a una fila que no tiene “padre”.

Si pudieras tener filas huérfanas podrían darse estos casos:

  • Durante el año le cobraste varias veces a un cliente, luego borraste los datos de ese cliente, y la pregunta es: ¿a quién cornos le cobraste si no encuentras su nombre por ningún lado?
  • Durante el año vendiste varias veces un producto, luego borraste los datos de ese producto, y la pregunta es: ¿cuál producto vendiste si su nombre no puedes encontrar por ningún lado?
  • Etc., etc., etc.

Desde luego que podrías obtener esa información mirando informes antiguos, o restaurando backups, o quizás estrujando tu cerebro para hallarla en tu memoria pero el problema es que en esos casos la información que necesitas estará afuera de tu Base de Datos. Y eso es un error gravísimo.

Porque la información necesaria siempre debería estar adentro de tu Base de Datos.

Cuando relacionas dos tablas entre sí usando una Foreign Key siempre el valor que insertas en la tabla “hija” ya existe en la tabla “padre”. Ok, hasta ahí muy bien, pero ¿qué ocurre si se quiere borrar o modificar una fila de la tabla padre que tiene filas relacionadas en la tabla “hija”?

Por ejemplo, queremos borrar los datos de un cliente, pero a ese cliente le hemos hecho cobranzas.

El Firebird te ofrece 3 posibilidades:

  1. Impedir
  2. Borrar o modificar también todas las filas relacionadas de la tabla “hija”. A esto se le llama “en cascada”.
  3. Poner un valor NULL o el valor por defecto en las filas de la tabla “hija”

La opción más común, la más usual, la más normal, la generalmente más correcta es la 1. O sea, no podrás borrar ni modificar una fila de la tabla “padre” si tiene filas relacionadas en la tabla “hija”.

La opción 2. puede ser útil cuando se modifica una fila de la tabla “padre”, pero extremadamente peligrosa cuando se la borra. Muchísimo cuidado y muchísima atención ahí. Borrar una fila de la tabla “padre” que no debería haberse borrado puede causar un desastre mayúsculo.

La opción 3. es muy raramente empleada porque en ese caso estarías evitando la ventaja de tener una Foreign Key.

¿Y si se tiene una necesidad legítima de borrar o modificar una fila de la tabla “padre”?

A veces, realmente se necesita borrar una fila de la tabla “padre”. Bien, es posible hacerlo. Primero se borran todas las fijas relacionadas de la tabla “hija” (o de las tablas “hijas”, porque podrían ser varias) y luego se borra la fila de la tabla “padre”.

Como ves, algo así no puede ocurrir “por accidente”. Si se hizo todo ese trabajo es porque (se supone) que se sabía muy bien lo que se estaba haciendo. (Si no se sabía, y se hizo una tremenda idiotez, al menos no tendrán excusa)

¿Y siempre hay que relacionar tablas?

No, no siempre. Muchas veces sí es posible hacerlo pero no es conveniente hacerlo.

¿Y cuándo no es conveniente relacionar a dos tablas entre sí?

Cuando la tabla “padre” tiene muy pocas filas y la tabla “hija” tiene muchas filas.

Por ejemplo, una empresa tiene 2 sucursales. Por lo tanto la tabla de SUCURSALES tiene 2 filas. Y en la tabla de VENTAS se guarda el código de una de esas sucursales. En ese caso, es un error hacer la relación mediante una Foreign Key. ¿Y por qué es un error? por algo que en Firebird se llama “selectividad de los índices”. Nunca es conveniente relacionar cuando una de las tablas tiene muy pocas filas.

¿Y en ese caso, cómo se evitaría que alguien borre a una Sucursal cuyo código existe en la tabla de VENTAS?

Mediante un trigger BEFORE DELETE de la tabla de SUCURSALES.

IF (EXISTS(SELECT VTC_CODSUC FROM VENTASCAB WHERE VTC_CODSUC = OLD.SUC_CODIGO)) THEN
   EXCEPTION E_Sucursal_En_Uso;

En este caso, antes de borrar una Sucursal se verifica que no se la haya usado en la tabla VENTASCAB. Si se la usó entonces se lanzará una excepción y no será borrada. Desde luego que normalmente habrá muchos IF … THEN, uno por cada tabla donde se guarda el código de la Sucursal.

Por lo tanto, la regla es:

  • Si la tabla “padre” tiene muchas filas, usar una restricción Foreign Key para relacionarla con la tabla “hija” y evitar que la fila “padre” sea borrada o modificada por un “accidente” cometido por un descerebrado.
  • Si la tabla “padre” tiene pocas filas, usar un trigger BEFORE DELETE para evitar que se borre una de sus filas si es que ese valor existe en otra tabla. Y un trigger BEFORE UPDATE para evitar que se modifique su valor.

Artículos relacionados:

Entendiendo a las Foreign Keys

Selectividad de los índices

Entendiendo las excepciones

El índice del blog Firebird21

El foro del blog Firebird21

 

Entendiendo a las Foreign Keys

14 comentarios

Una Foreign Key (en castellano: clave extranjera o clave foránea) es una columna (o varias columnas) de una tabla que se corresponden exactamente con una columna (o varias columnas) de otra tabla donde están definidas como Primary Key o como Unique

De esta forma se arma una relación padre/hijo, también conocida como maestro/detalle o como cabecera/detalle entre ambas tablas.

¿Para qué sirven las Foreign Keys?

Para asegurarnos que en la tabla hija solamente se graben valores que existen en la tabla padre.

Así, nunca tendríamos una venta que no se sabe a cual cliente se le vendió, o un producto que se vendió pero no se sabe el número de la Factura de venta, o un alumno a quien se le tomó un examen pero no se sabe cual examen se le tomó, o un cheque se emitió pero no se sabe el nombre del beneficiario de ese cheque, etc.

Si usamos Foreign Keys el mismo Firebird nos impedirá que grabemos una fila hija si no existe el padre de dicha fila. Por lo tanto, sería imposible que nos encontremos con errores como los descritos arriba.

Es una importantísima medida de seguridad y como tal es altamente recomendable que usemos Foreign Keys porque eso le dará mucha confiabilidad a nuestra Base de Datos.

¿Cómo se crea una Foreign Key?

Primero, la tabla padre debe existir y tener un identificador único (este identificador único puede ser una restricción Primary Key o una restricción Unique)

Segundo, a la tabla hija se le agrega una columna (o más de una columna) que correspondan al mismo tipo de datos que la Primary Key o la Unique de la tabla padre. Eso significa que si el identificador de la tabla padre es BIGINT, el identificador que se usa en la tabla hija para relacionarlo con la tabla padre también debe ser BIGINT. Si el identificador del padre es INTEGER, el identificador en la tabla hija usado para la relación también debe ser INTEGER, etc. Los nombres de los identificadores no importan, pero los tipos de datos sí, pues deben ser idénticos.

Ejemplo:

(Para facilitar la comprensión utilizaremos el programa EMS SQL Manager, pero cualquier otro programa administrador gráfico también servirá o podríamos hacer todo esto manualmente con el programa ISQL que viene incluido con el Firebird.)

FOREIGN1

Captura 1. Si haces clic en la imagen la verás más grande

En esta imagen se muestran dos columnas de la tabla CLIENTES (tiene muchas más columnas, pero no nos interesan ahora). Como se puede ver, el tipo de datos de la columna CLI_IDENTI (identificador del cliente) es de tipo BIGINT. Esta es la tabla que actuará como padre.

FOREIGN2

Captura 2. Si haces clic en la imagen la verás más grande

Esta tabla se llama MOVIMCAB y se usa para guardar en ella las cabeceras de los movimientos. Aquí, la columna donde se grabará el identificador del cliente también es de tipo BIGINT, por lo tanto podemos utilizarla sin problemas. Fíjate que esta tabla tiene dos identificadores: el que identifica a cada movimiento ocurrido y el que identifica a cada cliente.

Si hacemos clic en la pestaña “Constraints” y luego clic en la pestaña “Foreign Keys” y luego clic con el botón derecho, veremos este menú contextual:

FOREIGN3

Captura 3. Si haces clic en la imagen la verás más grande

haciendo clic allí veremos una ventana de nombre “Constraints” la cual nos permite definir a la nueva Foreign Key. Podemos elegir su nombre, la columna o columnas de la tabla hija, el nombre de la tabla padre y la o las columnas de la tabla padre, y las reglas que se aplicarán cuando se borre o se modifique una fila de la tabla padre.

FOREIGN4

Captura 4. Si haces clic en la imagen la verás más grande

Como puedes ver en la Captura 4., se relacionó la columna MVC_IDECLI con la columna CLI_IDENTI de la tabla CLIENTES. ¿Qué significa eso? que desde este momento en la columna MVC_IDECLI solamente se podrá ingresar un número que ya exista en la columna CLI_IDENTI.

¿Qué son las reglas de integridad?

Si miras en la parte inferior de la imagen de arriba verás que dice “On Delete Rule” y “On Update Rule“. ¿Qué significa eso?

Son las acciones que debe realizar el Firebird cuando se modifica (UPDATE) o se borra (DELETE) una fila de la tabla padre. Esas reglas de integridad son las siguientes:

NO ACTION

Es la opción por defecto. Impide que el identificador de la tabla padre sea cambiado o que la fila padre sea borrada. Si se intenta una de esas operaciones el Firebird se enojará.

FOREIGN5

Captura 5. Si haces clic en la imagen la verás más grande

Foreign key references are present for the record” significa que hay al menos una fila en alguna tabla hija (porque una tabla padre puede tener muchas tablas hijas) con el mismo identificador que se quiso cambiar o borrar. Como la regla de integridad es NO ACTION, eso no está permitido.

 ON UPDATE CASCADE

Si se cambia el identificador de la tabla padre también se cambia el identificador relacionado en la tabla hija. Cuidado con esto porque si la tabla hija tiene millones de filas entonces cambiar todos los identificadores puede llevar un buen tiempo. Recuerda que en este caso al cambiar el identificador de la fila padre, se cambian los identificadores de todas las filas hijas, en todas las tablas que tengan la regla de integridad ON UPDATE CASCADE. En general, no es bueno hacer algo así.

FOREIGN6

Captura 6. Si haces clic en la imagen la verás más grande

En la Captura 6. podemos ver los valores originales del identificador del cliente. En ambos casos el identificador del cliente es 1 y está todo ok, así debe ser. En la columna MVC_IDECLI de la tabla MOVIMCAB se introdujo el número 1 y como hay un cliente que tiene ese identificador la grabación se realizó sin problemas. Después se cambió el identificador en la tabla CLIENTES, se reemplazó el 1 por un 2 y el Firebird automáticamente cambió el valor de la columna MVC_IDECLI, como podemos ver en la imagen de abajo.

FOREIGN7

Captura 7. Si haces clic en la imagen la verás más grande

El cambio de identificador en la columna MVC_IDECLI fue automático. Luego de cambiar el identificador en CLI_IDENTI de 1 a 2 y ejecutar el COMMIT correspondiente, ese mismo número 2 apareció en la columna MVC_IDECLI.

ON DELETE CASCADE

Si se borra una fila de la tabla padre también se borran todas las filas relacionadas de la tabla hija. ¡¡¡Cuidado con esto!!! porque puede ser muy peligroso. En nuestro ejemplo, si borras al cliente también estarás borrando todas las ventas que se le hicieron a ese cliente. Hay que pensarlo muy bien antes de establecer como regla de integridad a ON DELETE CASCADE, porque un error de apreciación puede ser trágico.

SET NULL

El identificador en la tabla hija es puesto a NULL. Eso implica que la fila hija se convierte en huérfana ya que no tiene padre. Evidentemente esta regla de integridad no puede ser aplicada si la columna de la tabla hija no admite NULL.

SET DEFAULT

El identificador de la tabla hija es puesto a su valor por defecto. Cuando definimos una columna podemos establecer que tendrá un valor por defecto cuando ningún valor le es asignado explícitamente. Ese será el valor que tendrá el identificador de la tabla hija cuando el identificador de la tabla padre sea cambiado o la fila padre sea borrada. Algunos puntos que se deben recordar son:

      1. El valor por defecto es el que estaba siendo usado cuando la restricción Foreign Key fue definida. Si más tarde se cambió ese valor por defecto no importa, el valor original será el utilizado. Cuidado con esto.
      2. Si ningún valor por defecto fue declarado para la columna entonces su valor por defecto es NULL. En ese caso, es lo mismo que usar la regla de integridad SET NULL
      3. Si el valor por defecto no existe en el identificador de la tabla padre, ocurrirá un error

¿Cómo se puede ver el comando SQL que crea a la Foreign Key?

En la misma ventana llamada “Constraints“, a la izquierda, hay una opción rotulada “DDL“. Haciendo click en ella nos muestra el comando SQL correspondiente a esa Foreign Key, como puedes ver en la imagen de abajo:

FOREIGN8

Captura 8. Si haces clic en la imagen la verás más grande

Nos dice que se modificó la tabla MOVIMCAB para agregarle una restricción llamada FK_MOVIMCAB, la cual es una Foreign Key que relaciona a la columna MVC_IDECLI de la tabla MOVIMCAB con la columna CLI_IDENTI de la tabla CLIENTES.

Y aquí está la forma en que se crea una tabla y sus restricciones si no usas un administrador gráfico (en este artículo se usó EMS SQL Manager, hay varios más, para ser productivo es aconsejable que uses alguno porque ahorrarás muchísimo tiempo). Esto es lo que escribirías en el programa ISQL o en un script:

CREATE DOMAIN D_IDENTIFICADOR AS BIGINT;

CREATE DOMAIN D_CHAR3 AS CHAR(3);

CREATE DOMAIN D_FECHA2010 AS DATE CHECK (VALUE >= '01-01-2010');

CREATE TABLE MOVIMCAB (
   MVC_IDENTI D_IDENTIFICADOR NOT NULL,
   MVC_TIPMOV D_CHAR3,
   MVC_IDECLI D_IDENTIFICADOR,
   MVC_FECHAX D_FECHA2010);

ALTER TABLE MOVIMCAB ADD CONSTRAINT PK_MOVIMCAB PRIMARY KEY (MVC_IDENTI);

ALTER TABLE MOVIMCAB ADD CONSTRAINT FK_MOVIMCAB FOREIGN KEY (MVC_IDECLI) REFERENCES CLIENTES(CLI_IDENTI);

 Conclusión:

Debemos usar mucho a las Foreign Keys porque con ellas se consigue que el contenido de nuestra Base de Datos sea altamente confiable. Si las usamos evitaremos que nos falten datos o que se realicen acciones inconsistentes, como borrar un producto del cual tenemos ventas registradas, o un alumno que ya se inscribió, o una cuenta contable que ya fue usada en un asiento, etc.

La regla de integridad NO ACTION es la más restrictiva y la que en general es mejor aplicar para no tener filas huérfanas, algo que en muchos casos puede ser muy malo, por eso es la regla de integridad por defecto.

Artículos relacionados:

El índice del blog Firebird21

El foro del blog Firebird21