Programación defensiva

La tarea más importante que tenemos cuando diseñamos una Base de Datos es evitar que entre basura en ella. Todo lo demás que hagamos será inservible si permitimos la entrada de basura. Se llama «basura» a cualquier dato que está pero que no debería estar.

Ejemplos de basura

• La empresa inició sus actividades el 11 de octubre de 2009 y hay una venta con fecha 22 de marzo de 2007
• La venta se hizo el día 14 de mayo de 2010 y la cobranza el día 20 de febrero de 2010
• El precio de costo de un producto es mayor que su precio de venta
• Se tiene registrada una venta, pero no la fecha de esa venta
• Se tiene registrada la venta de un producto, pero el identificador de ese producto no existe en la tabla de PRODUCTOS
• Se tiene registrada una venta, pero no se registró el identificador del cliente a quien se le vendió

Estos son solamente algunos ejemplos, hay miles más que podrían agregarse. El común denominador de todos ellos es que hay datos faltantes o datos inconsistentes. Un dato es inconsistente cuando es ilógico, cuando algo así no podría ocurrir. Por ejemplo, no se puede vender antes de constituir la empresa, ni se puede cobrar antes de vender, eso no tiene sentido.

Programando defensivamente

La programación defensiva tiene como ventaja que evita la introducción de basura (o al menos disminuye muchísimo la probabilidad de que ocurra) y la desventaja que nos hace trabajar más.

Pero como nuestra principal tarea es evitar la introducción de basura ese trabajo de más es el precio que deberemos pagar.

En la programación defensiva lo que hacemos es poner trabas en cuantos lugares sean posibles a los datos faltantes o inconsistentes. En la programación normal pondríamos una sola traba (aunque un mal diseñador ni siquiera haría eso), en cambio en la programación defensiva pondremos muchas trabas.

Ejemplo 1

Queremos evitar que el precio de costo sea mayor que el precio de venta.

Creamos un dominio llamado D_PRECIO:

CREATE DOMAIN D_PRECIO AS
   NUMERIC(18, 4)
   CHECK (VALUE >= 0);

Este dominio aún no nos asegura que el precio de venta sea mayor que el precio de costo, pero sí que no se introduzcan precios negativos. Algo es algo.

Ahora le agregamos una restricción CHECK a la tabla de PRODUCTOS:

ALTER TABLE PRODUCTOS
   ADD CONSTRAINT CHK_PRODUCTOS
   CHECK (PRD_PREVTA > PRD_PRECTO);

Esta restricción CHECK nos asegurará que siempre el precio de venta sea mayor que el precio de costo. ¿Ya está bien, verdad? ¿Es suficiente con esta restricción? Pues sí, pero queremos programar defensivamente para duplicar las comprobaciones y evitar la introducción de basura, entonces también escribimos un trigger before insert (o sea un trigger que se ejecutará antes de que la fila sea grabada en la tabla):

CREATE TRIGGER BIU_PRODUCTOS FOR PRODUCTOS
   ACTIVE BEFORE INSERT OR UPDATE
   POSITION 1
AS
BEGIN

   IF (NEW.PRD_PREVTA <= NEW.PRD_PRECTO) THEN
      EXCEPTION E_PRECIO_VENTA_MENOR_QUE_COSTO ;

END;

Y a la excepción E_PRECIO_VENTA_MENOR_QUE_COSTO podríamos definirla como:

CREATE EXCEPTION E_PRECIO_VENTA_MENOR_QUE_COSTO
   'El precio de venta no es mayor que el precio de costo';

Ejemplo 2

Queremos asegurarnos que el identificador del producto que vendemos exista en la tabla de PRODUCTOS

Primero, agregamos una FOREIGN KEY a nuestra tabla de movimientos de productos (o sea, la tabla en la cual registramos las ventas)

ALTER TABLE MOVIMDET
   ADD CONSTRAINT FK_MOVIMDET1
   FOREIGN KEY (MOV_CODSUC, MOV_IDEPRD)
   REFERENCES PRODUCTOS(PRD_CODSUC, PRD_IDENTI);

Ya está, ¿verdad? con esto nos aseguramos que no se pueda vender un producto cuyo identificador no se encuentre en la tabla de PRODUCTOS. Muy bien, funcionará, pero somos exquisitos y queremos aumentar la seguridad, entonces también escribimos una restricción CHECK en la tabla MOVIMDET

ALTER TABLE MOVIMDET
   ADD CONSTRAINT CHK_MOVIMDET CHECK (
      MOV_CODSUC IN (SELECT
                        SUC_CODIGO
                     FROM SUCURSALES) AND
      MOV_IDEPRD IN (SELECT
                        PRD_IDENTI
                     FROM
                        PRODUCTOS
                     WHERE
                        PRD_CODSUC = MOVIMDET.MOV_CODSUC)
);

Esta restricción CHECK nos asegurará que el código de la sucursal se encuentre en la tabla de SUCURSALES y que el identificador del producto se encuentre en la tabla de PRODUCTOS. ¿Ya está bien, ya es suficiente? Pues no, estamos programando defensivamente así que también escribiremos un trigger que se ejecutará antes de insertar o actualizar filas:

CREATE TRIGGER BIU_MOVIMDET FOR MOVIMDET
   ACTIVE BEFORE INSERT OR UPDATE
   POSITION 1
AS
   DECLARE VARIABLE lnCodigoSucursal TYPE OF D_CODIGOSUCURSAL;
   DECLARE VARIABLE lnIdentificadorProducto TYPE OF D_IDENTIFICADOR;
BEGIN

   lnCodigoSucursal = (SELECT
                          SUC_CODIGO
                       FROM
                          SUCURSALES
                       WHERE
                          SUC_CODIGO = NEW.MOV_CODSUC);

   IF (lnCodigoSucursal IS NULL) THEN
      EXCEPTION E_SIN_SUCURSAL;

   lnIdentificadorProducto = (SELECT
                                 PRD_IDENTI
                              FROM
                                 PRODUCTOS
                              WHERE
                                 PRD_CODSUC = NEW.MOV_CODSUC AND
                                 PRD_IDENTI = NEW.MOV_IDEPRD);

   IF (lnIdentificadorProducto IS NULL) THEN
      EXCEPTION E_SIN_PRODUCTO;

END;

¿Qué hicimos en este trigger? Primero, averiguamos si el código de la Sucursal que se quiere grabar en la columna MOV_CODSUC existe en la tabla de SUCURSALES. Si el resultado de la búsqueda es NULL eso significa que no existe y por lo tanto enviamos una excepción. Al enviar una excepción el código finaliza con error, todo el código fuente que se encuentre después de la excepción jamás se ejecutará porque el control regresó al nivel superior. Si la sucursal existe entonces continuamos y ahora averiguamos si existe un producto con ese identificador en esa sucursal. Si el resultado de la búsqueda es NULL, eso significa que no existe, y por lo tanto enviamos una excepción.

Recuerda que si un trigger envía una excepción las columnas no se graban en la tabla. Al enviar una excepción le estamos diciendo al Firebird: «aquí encontré un error, no grabes estas columnas en la tabla porque hay algo que está mal».

Conclusión:

La programación defensiva es una herramienta que tenemos para conseguir que nuestro código fuente sea más confiable. Siempre debemos pensar que los usuarios son muy tontos o muy malos, tipos que pueden hacer un desastre por ignorancia o por maldad, y debemos tomar todas las precauciones habidas y por haber para evitar que causen problemas.

Entonces, en nuestro lenguaje de programación ya les debemos poner trabas a sus acciones, pero también debemos hacerlo en la Base de Datos. Firebird nos provee de cinco herramientas muy buenas para evitar que introduzcan basura en nuestras tablas: los dominios, las restricciones foreign key, las restricciones check, las restricciones unique keys y los triggers.

Debemos usarlas y quitarles el máximo provecho, que para eso están.

La programación defensiva nos hará trabajar más, pero también nos dará mayor tranquilidad, porque les tendremos mucha confianza a los datos que están guardados en las tablas.

Y si nuestras aplicaciones son muy robustas tendremos clientes muy contentos. Y clientes muy contentos siempre significa ganar más dinero.

Artículos relacionados:

Entendiendo a los dominios

Entendiendo a las Foreign Keys

Usando la restricción CHECK

Entendiendo las excepciones

Entendiendo la integridad referencial

Entendiendo a los triggers

El índice del blog Firebird21

Asegurando tener datos consistentes en la Base de Datos

Lo peor que le podría ocurrir a una Base de Datos es que tenga datos inconsistentes, es decir: que sean contradictorios o que falten datos que deberían estar o que estén datos que no deberían estar.

Firebird nos provee de varias herramientas que podemos usar para asegurar la consistencia, ellas son:

• Primary Key
• Foreign Key
• Unique Key
• Check
• Trigger
• Stored procedure

Con la Primary Key podemos identificar sin dudas a cada fila de la tabla. Lo ideal es que la Primary Key sea numérica y autoincremental.

Con la Foreign Key aseguramos que el valor de una columna (de la tabla hija) exista en otra columna (de la tabla padre). De esta manera siempre podremos relacionar a ambas tablas entre sí, porque tienen datos comunes.

Con la Unique Key sabemos que no habrá valores duplicados en la columna. Todos los valores serán únicos en esa columna de esa tabla.

Con el Check podemos hacer validaciones relativamente simples. Impedimos que se introduzcan valores que sabemos que no deberían estar.

Con el Trigger podemos hacer validaciones simples o muy complicadas. Esto último porque nos permite tener variables locales y entonces las condiciones de validación pueden ser tan complejas como necesitemos. También podemos usarlos para colocar el valor de una columna o para insertar, modificar o borrar filas de otras tablas.

Con el Stored procedure podemos también validar antes de realizar la operación de inserción, actualización o borrado.

Validando en un stored procedure

Aunque podemos usar los stored procedures para hacer validaciones eso no es lo aconsejable. ¿Por qué no? porque alguien podría saltarse esa validación muy fácilmente. Por ejemplo, para insertar una fila en la tabla de BANCOS podríamos tener un stored procedure que se encargue de esa tarea, y entonces en lugar de escribir:

EXECUTE PROCEDURE GRABAR_BANCO(17, 'Citibank');

alguien podría simplemente escribir:

INSERT INTO BANCOS (BAN_CODIGO, BAN_NOMBRE) VALUES (17, 'Citibank');

y así se saltaría cualquier validación que hubiéramos escrito en el stored procedure. En otras palabras las validaciones que escribimos en el stored procedure no sirvieron, nuestro trabajo no sirvió para validar.

En cambio, nadie puede saltarse la validación que escribamos en un trigger. Lo que se escribe en un trigger siempre se ejecuta.

¿Es conveniente validar en un check o en un trigger?

Podemos validar con un check cuando la validación sea relativamente simple. Por ejemplo: que el precio de venta siempre sea mayor que cero, que la fecha siempre sea igual o posterior al «01/ENE/2010», que todos los productos siempre tengan nombre, que el sexo de la persona siempre sea «F» o «M», que la cobranza siempre se haga a facturas que tienen saldo, etc.

Con un trigger podemos validar todos los casos anteriores y también cuando las validaciones son más complejas. Por ejemplo: que la fecha de la cobranza siempre sea igual o posterior a la fecha de la venta, que el descuento solamente se aplique a clientes a quienes ya se les vendió por más de 4.000 dólares y la última venta fue hace menos de 90 días y ya se les cobró al menos el 75% de lo vendido.

Además, si usamos un trigger para validar tenemos otra ventaja: podemos mostrarle al usuario mensajes personalizados. Si un check falla el mensaje que nos enviará el Firebird siempre será:

«Operation violates CHECK constraint»

en cambio, si usamos un trigger lanzamos una excepción cuando descubrimos un error y en esa excepción podemos escribir cualquier texto que deseemos, teniendo así mensajes de error personalizados.

Conclusión:

Una Base de Datos cuyo contenido es inconsistente es lo peor que puedes tener porque ninguna información que obtengas será confiable. Por ese motivo hay que evitar a toda costa tener inconsistencias en los datos. Firebird nos provee de varias herramientas muy útiles y debemos utilizarlas y sacarles el máximo provecho que para eso están.

Artículos relacionados:

Entendiendo a las Primary Keys

Claves primarias: ¿simples o compuestas?

Entendiendo a las Foreign Keys

Entendiendo la integridad referencial

Usando la restricción CHECK

Entendiendo a los Stored Procedures

Entendiendo a los triggers

Entendiendo las excepciones

El índice del blog Firebird21

Error: Foreign key references are present for the record

Este error ocurre cuando una tabla tiene una restricción Foreign Key y quieres borrar una fila de la tabla padre que está siendo referenciada en la tabla hija y la Foreign Key no es «on cascade».

Artículo relacionado:

El índice del blog Firebird21

Entendiendo la Integridad Referencial

La «integridad referencial» es un concepto utilizado en las bases de datos y significa que la relación entre dos tablas siempre es consistente.

¿Qué significa la palabra «integridad»?

Que no pueden sobrar ni faltar datos, deben estar todos, ninguno más y ninguno menos.

¿Qué significa la palabra «referencia»?

Que conociendo algunos datos de una tabla se pueden conocer todos los datos de la otra tabla.

¿Cómo se establece la relación entre las dos tablas?

A través de una restricción Foreign Key.

En una restricción Foreign Key una de las tablas actúa como «padre» y la otra tabla actúa como «hija». La relación entre ambas tablas está dada por una columna (o más de una columna) cuyos tipos de datos son idénticos. Por ejemplo, se puede establecer entre una columna INTEGER de la tabla «padre» y una columna INTEGER de la tabla «hija», pero no se puede entre una columna INTEGER de la tabla «padre» y una columna VARCHAR de la tabla «hija».

• SMALLINT con SMALLINT, ok
• INTEGER con INTEGER, ok
• BIGINT con BIGINT, ok
• SMALLINT con VARCHAR, error
• INTEGER con FLOAT, error
• VARCHAR con DATE, error
• etc.

¿Qué es una relación consistente?

Aquella en la cual cada fila de la tabla «hija» tiene una fila y solamente una fila correspondiente en la tabla «padre». Si una fila de la tabla «hija» no tiene una fila (y solamente una fila) correspondiente en la tabla «padre» entonces ya no existe la integridad referencial entre ambas tablas.

• 1 fila de la tabla «hija» se corresponde con 0 filas de la tabla «padre» —> no hay integridad referencial
• 1 fila de la tabla «hija» se corresponde con 1 fila de la tabla «padre» —> sí hay integridad referencial
• 1 fila de la tabla «hija» se corresponde con 2 ó más filas de la tabla «padre» —> no hay integridad referencial

¿Qué debemos tener en cuenta para establecer una integridad referencial?

Que la relación entre la tabla «hija» y la tabla «padre» se haga mediante la Primary Key de la tabla «padre» o de una Unique Key de la tabla «padre».

¿Qué implica que entre dos tablas exista una integridad referencial?

1. Que no puedes agregarle una fila a la tabla «hija» si no existe la fila correspondiente en la tabla «padre»
2. Que no puedes cambiar la columna usada en la referencia en la tabla «padre» (a menos que hayas establecido «actualización en cascada»)
3. Que no puedes borrar la fila usada en la referencia en la tabla «padre» (a menos que hayas establecido «borrado en cascada»)

¿Qué significan «actualización en cascada» y «borrado en cascada»?

• «Actualización en cascada» significa que si cambias el valor de la columna en la tabla «padre» ese mismo cambio se efectuará automáticamente en todas las filas de la tabla «hija» correspondientes
• «Borrado en cascada» significa que si borras una fila de la tabla «padre» todas las filas correspondientes de la tabla «hija» también serán borradas.

Ejemplo:

Tenemos una tabla llamada PAISES y otra tabla llamada PRODUCTOS. Queremos que la tabla PRODUCTOS referencie a la tabla PAISES, para poder así saber cual es el país del cual proceden nuestros productos.

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

La Primary Key de esta tabla está compuesta por las columnas PAI_CODSUC y PAI_IDENTI

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

Podemos establecer una restricción «Foreign Key» entre ambas tablas usando las dos columnas que tienen en común: Código de la Sucursal e Identificador del País:

ALTER TABLE PRODUCTOS ADD CONSTRAINT FK_PRODUCTOS3 FOREIGN KEY (PRD_CODSUC,PRD_IDEPAI) REFERENCES PAISES(PAI_CODSUC,PAI_IDENTI);

Como ambas tablas están relacionadas mediante una «Foreign Key», entonces:

1. No podemos colocar en la columna PRD_IDEPAI un número que no exista en la columna PAI_IDENTI. O sea que el número que coloquemos en la columna PRD_IDEPAI debe existir en la columna PAI_IDENTI
2. No podemos colocar NULL en la columna PRD_IDEPAI
3. No podemos cambiar el número que se encuentra en la columna PAI_IDENTI si ese número existe en alguna columna PRD_IDEPAI
4. No podemos borrar una fila de la tabla PAISES si el número de su columna PAI_IDENTI existe en la columna PRD_IDEPAI de alguna fila de la tabla PRODUCTOS

El punto 3. podemos hacerlo si establecimos «actualización en cascada», ejemplo:

ALTER TABLE PRODUCTOS ADD CONSTRAINT FK_PRODUCTOS3 FOREIGN KEY (PRD_CODSUC,PRD_IDEPAI) REFERENCES PAISES(PAI_CODSUC,PAI_IDENTI) ON UPDATE CASCADE;

El punto 4. podemos hacerlo si establecimos «borrado en cascada», ejemplo:

ALTER TABLE PRODUCTOS ADD CONSTRAINT FK_PRODUCTOS3 FOREIGN KEY (PRD_CODSUC,PRD_IDEPAI) REFERENCES PAISES(PAI_CODSUC,PAI_IDENTI) ON DELETE CASCADE;

Conclusión:

Siempre que sea posible debemos tener tablas con «integridad referencial» porque eso nos asegura de tener datos consistentes en las tablas «hijas».

Como no se puede guardar en una tabla «hija» un valor que no exista en su tabla «padre» eso nos da la seguridad de que todos los valores de la tabla «hija» existan en la tabla «padre» y evitamos así tener filas «huérfanas» (se llaman así a las que no tienen «padre»). Las filas huérfanas solamente pueden causar problemas y ningún beneficio.

Artículos relacionados:

Entendiendo a las Foreign Keys

El índice del blog Firebird21

Borrando filas de detalle en Maestro/detalle

Si tenemos dos tablas: Maestro y Detalle, podemos determinar lo que ocurrirá cuando borramos (o intentamos borrar) una fila del Maestro según lo que hayamos especificado en la Foreign Key, tal como se explica en este artículo:

https://firebird21.wordpress.com/2013/05/26/entendiendo-a-las-foreign-keys/

Sin embargo, a veces necesitamos borrar las filas del Detalle cuando las filas del Maestro cumplen con alguna condición. Y como la Foreign Key no tiene la regla de CASCADE para borrado, no nos sirve borrar la fila del Maestro.

Por ejemplo, queremos borrar las filas de detalle cuando:

• La fecha de la venta es 26/JUN/2013
• El identificador del cliente es 12345
• La moneda es dólares americanos

Para estos casos es muy útil el operador IN, como vemos a continuación:

DELETE FROM
   VENTASDET D
WHERE
   D.VEN_IDECAB IN (SELECT C.VTC_IDENTI FROM VENTASCAB C WHERE C.VTC_IDECLI = 12345)

VENTASDET es la tabla de detalles de las ventas

VENTASCAB es la tabla cabecera (o maestro) de las ventas

VEN_IDECAB es la columna de VENTASDET donde se guarda el identificador de la cabecera

VTC_IDENTI es el identificador de la cabecera

VTC_IDECLI es el identificador del cliente

Este comando DELETE borrará los detalles de todas las ventas que se le hicieron al cliente que tiene identificador 12345. Luego, si es necesario habría que escribir:

DELETE FROM VENTASCAB WHERE VTC_IDECLI = 12345

para borrar también las filas de cabecera de las ventas realizadas a ese cliente.

Desde luego que la forma más fácil y sencilla de conseguir esto es que la Foreign Key sea ON DELETE CASCADE para que al borrar una fila del maestro se borren todas las correspondientes filas de detalle. Pero si la Foreign Key no es ON DELETE CASCADE y no podemos cambiarla entonces aquí se mostró una posible solución al problema de borrar filas de detalles cuando las filas de cabecera cumplen con una condición.

Artículo relacionado:

El índice del blog Firebird21