Too Cool for Internet Explorer

SQLite y las claves ajenas

Hora y Fecha: Septiembre 25, 2006 @ 7:02 pm Autor: Moisés Maciá
Categorías:
859 views

Ya he dado carpetazo a Microsoft Access, estoy probando con SQLite (como debería haber hecho desde un principio) y de momento funciona perfecto con ADO.Net. Como ya tenía encapsulado el acceso a datos solamente he tenido que cambiar un par de lineas del programa para que funcionara con la nueva base de datos, más toda la morralla de sintaxis SQL de Access, obviamente …

Peeeero no todo son florecillas y mariposas, algún gnuero no identificado me dijo que SQLite implementaba integridad referencial mediante claves ajenas y toda la pesca. Pues no, tal y como dice la documentación acepta la sintaxis pero hace caso omiso de las referencias.

El caso es que me hacían mucha falta porque paso olímpicamente de comprobar a mano todas las tablas cada vez que actualizo, borro o inserto registros, ya he sufrido suficiente con MySQL este tema como para volver otra vez.

Vale, no tendrá integridad referencial pero con la dosis justa de cafeína y un rato peleando con los triggers he conseguido un comportamiento idéntico. MMWAHAHAHA!!

Veamos, pondré un ejemplo con dos tablas: empleados y departamentos; creo que la relación es obvia así que no comento nada más al respecto y paso a escribir la sentencia para crear las tablas en SQLite.

  1. CREATE TABLE departments(
  2.   id INTEGER PRIMARY KEY AUTOINCREMENT,
  3.   name VARCHAR(50)
  4. );  
  5.  
  6. CREATE TABLE employees(
  7.   id INTEGER PRIMARY KEY AUTOINCREMENT,
  8.   name VARCHAR(50),
  9.   department_id INTEGER NOT NULL
  10.     CONSTRAINT fk_department_id REFERENCES departments(‘id’) ON DELETE CASCADE );

En la declaración aparece una relación entra la tabla empleados y la tabla departamentos a través de su clave primaria, pero SQLite lo ignora por completo. Para poder asegurar la integridad de dicha relación obligatoriamente debemos utilizar una serie de triggers. Los triggers son una característica añadida a partir de la versión 2.5 de SQLite, actualmente andan por la versión 3.3.x asi que no debería haber problema si lo empleáis en sistemas modernos.

Cada restricción de clave debe tener tres triggers (tres tristes triggers … oh wait!): uno para las sentencias INSERT, otro para UPDATE y otro para DELETE.

Trigger para INSERT (ojo, permite valores nulos):

  1. CREATE TRIGGER fki_employees_department_id
  2. BEFORE INSERT ON employees
  3. FOR EACH ROW BEGIN
  4.   SELECT CASE
  5.     WHEN ((SELECT id FROM departments WHERE id = NEW.department_id) IS NULL)
  6.       THEN RAISE (ABORT,‘violacion de la restricción de integridad  "fk_department_id" por una sentencia INSERT’)
  7.   END;
  8. END;

En caso de que no queramos valores nulos en el campo, cambiamos ligeramente el código añadiendo una nueva condición:

  1. CREATE TRIGGER fki_employees_department_id
  2. BEFORE INSERT ON employees
  3. FOR EACH ROW BEGIN
  4.   SELECT CASE
  5.     WHEN ((NEW.department_id IS NOT NULL) AND (SELECT id FROM departments WHERE id = NEW.department_id) IS NULL)
  6.       THEN RAISE (ABORT,‘violacion de la restricción de integridad  "fk_department_id" por una sentencia INSERT’)
  7.   END;
  8. END;

Exactamente lo mismo para las sentencias UPDATE:

  1. CREATE TRIGGER fku_employees_department_id
  2. BEFORE UPDATE ON employees
  3. FOR EACH ROW BEGIN
  4.   SELECT CASE
  5.     WHEN ((SELECT id FROM departments WHERE id = NEW.department_id) IS NULL)
  6.       THEN RAISE (ABORT,‘violacion de la restricción de integridad  "fk_department_id" por una sentencia INSERT’)
  7.   END;
  8. END;

Para las sentencias DELETE, cambiamos el orden y aplicamos el trigger a la tabla departamentos:

  1. CREATE TRIGGER fkd_employees_department_id
  2. BEFORE DELETE ON departments
  3. FOR EACH ROW BEGIN
  4.   SELECT CASE
  5.     WHEN ((SELECT department_id FROM employees WHERE department_id = OLD.id) IS NOT NULL)
  6.     THEN RAISE(ABORT, ‘violación de la restricción de integridad "fk_department_id" por una sentencia DELETE)
  7.   END;
  8. END;

Este trigger previene el borrado de un departamento que contenga algún empleado, este suele ser el comportamiento habitual.

En ocasiones nos conviene eliminar todos los empleados en cascada cuando se elimina un departamento. El trigger que consigue este efecto quedaría de la siguiente manera:

  1. CREATE TRIGGER fkd_employees_department_id
  2. BEFORE DELETE ON departments
  3. FOR EACH ROW BEGIN
  4.   DELETE FROM employees WHERE department_id = OLD.id;
  5. END;

A que mola? :)





« Anterior post: Patentes de Software, de nuevo | Próximo post: Amazon Vs. O’Reilly »

4 Comentarios para “SQLite y las claves ajenas”

meneame.net
26 de Septiembre de 2006 a las 8:25 am    

SQLite y las claves ajenas…

Ya he dado carpetazo a Microsoft Access, estoy probando con SQLite (como debería haber hecho desde un principio) y de momento funciona perfecto con ADO.Net. Como ya tenía encapsulado el acceso a datos sólamente he tenido que cambiar un par de lineas…

BTO
26 de Septiembre de 2006 a las 9:14 am    

Acerca de MySQL

Creo que a partir de la version 5 ya se implementa la integridad referencial (tablas InnoDB)

Moisés Maciá
26 de Septiembre de 2006 a las 9:57 am    

Efectivamente, siempre que me toca utlizar MySQL lo hago a partir de la versión 5 o en su defecto con el motor de datos InnoDB

[...] SQLite pincha y gana por goleada Microsoft Access que es notablemente más rápido, alrededor de 4 veces más. Esto se debe a todos los triggers que le puse para asegurar la integridad referencial, Access lo hace solito. Si le quitamos los triggers SQLite sigue estando un poco por encima de Access. [...]


Bad Behavior has blocked 788 access attempts in the last 7 days.