SQLite: clave externa con eliminación en cascada

Los datos expuestos en este blog, son solo de índole informativo. Por favor realiza siempre una copia de seguridad antes de realizar cualquier cambio en tu proyecto.

SQLite: clave externa con eliminación en cascada

Este tutorial de SQLite explica cómo usar Claves foráneas eliminadas en cascada En SQLite, con sintaxis y ejemplos.

¿Qué es Foreign Dey con eliminación en cascada en SQLite?

Una clave externa para eliminaciones en cascada significa que si se elimina un registro en la tabla principal, el registro correspondiente en la tabla secundaria se eliminará automáticamente. Esto se llama eliminación en cascada en SQLite.

La eliminación en cascada de claves foráneas solo se puede definir en la sentencia CREATE TABLE.

propina: No puede usar ALTER TABLE para agregar una clave externa con una eliminación en cascada a una tabla porque SQLite no admite ADD CONSTRAINT en la instrucción ALTER TABLE. Sin embargo, le mostraremos una solución alternativa más adelante en este tutorial que le permite agregar una clave externa con funcionalidad de eliminación en cascada a una tabla existente.

Cómo crear una clave externa con eliminación en cascada usando la instrucción CREATE TABLE

sintaxis

La sintaxis para crear una clave externa con eliminación en cascada mediante la declaración CREATE TABLE en SQLite es:

CREATE TABLE table_name
(
  column1 datatype [ NULL | NOT NULL ],
  column2 datatype [ NULL | NOT NULL ],
  ...

  CONSTRAINT fk_column
    FOREIGN KEY (column1, column2, ... column_n)
    REFERENCES parent_table (column1, column2, ... column_n)
    ON DELETE CASCADE
);

ejemplo

Veamos un ejemplo de cómo crear una clave externa con eliminación en cascada usando la instrucción CREATE TABLE en SQLite.

Por ejemplo:

CREATE TABLE departments
( department_id INTEGER PRIMARY KEY AUTOINCREMENT,
  department_name VARCHAR
);

CREATE TABLE employees
( employee_id INTEGER PRIMARY KEY AUTOINCREMENT,
  last_name VARCHAR NOT NULL,
  first_name VARCHAR,
  department_id INTEGER,
  CONSTRAINT fk_departments
    FOREIGN KEY (department_id)
    REFERENCES departments(department_id)
    ON DELETE CASCADE
);

En este ejemplo, hemos creado una clave principal en la tabla de departamentos que contiene solo un campo: el campo id_departamento. Luego creamos una clave externa en la tabla de empleados llamada fk_departments que hace referencia a la tabla de departamentos según el campo de departamento_id.

Debido a las eliminaciones en cascada, cuando se elimina un registro en la tabla de departamentos, también se eliminan todos los registros con el mismo valor de department_id en la tabla de empleados.

Cómo agregar una clave externa a una tabla existente mediante la eliminación en cascada

No puede agregar claves foráneas con eliminaciones en cascada en SQLite usando la instrucción ALTER TABLE. En su lugar, debe cambiar el nombre de la tabla, crear una nueva tabla con la clave externa y copiar los datos en la nueva tabla.

sintaxis

La sintaxis para agregar una clave externa con capacidad de eliminación en cascada a una tabla existente en SQLite es:

PRAGMA foreign_keys=off;

BEGIN TRANSACTION;

ALTER TABLE table1 RENAME TO _table1_old;

CREATE TABLE table1
(
  column1 datatype [ NULL | NOT NULL ],
  column2 datatype [ NULL | NOT NULL ],
  ...

  CONSTRAINT fk_column
    FOREIGN KEY (column1, column2, ... column_n)
    REFERENCES parent_table (column1, column2, ... column_n)
    ON DELETE CASCADE
);

INSERT INTO table1 SELECT * FROM _table1_old;

COMMIT;

PRAGMA foreign_keys=on;

ejemplo

Primero, comencemos creando 2 tablas (departamentos y empleados):

CREATE TABLE departments
( department_id INTEGER PRIMARY KEY AUTOINCREMENT,
  department_name VARCHAR
);

CREATE TABLE employees
( employee_id INTEGER PRIMARY KEY AUTOINCREMENT,
  last_name VARCHAR NOT NULL,
  first_name VARCHAR,
  department_id INTEGER
);

A continuación, agreguemos algunos datos a estas tablas:

INSERT INTO departments VALUES (30, 'HR');
INSERT INTO departments VALUES (999, 'Sales');

INSERT INTO employees VALUES (10000, 'Smith', 'John', 30);
INSERT INTO employees VALUES (10001, 'Anderson', 'Dave', 999);

Ahora, agreguemos una clave externa con funcionalidad de eliminación en cascada a la tabla de empleados:

PRAGMA foreign_keys=off;

BEGIN TRANSACTION;

ALTER TABLE employees RENAME TO _employees_old;

CREATE TABLE employees
( employee_id INTEGER PRIMARY KEY AUTOINCREMENT,
  last_name VARCHAR NOT NULL,
  first_name VARCHAR,
  department_id INTEGER,
  CONSTRAINT fk_departments
    FOREIGN KEY (department_id)
    REFERENCES departments(department_id)
    ON DELETE CASCADE
);

INSERT INTO employees SELECT * FROM _employees_old;

COMMIT;

PRAGMA foreign_keys=on;

En este ejemplo, hemos creado una clave externa (con capacidad de eliminación en cascada) denominada fk_departments que hace referencia a la tabla de departamentos en función del campo de departamento_id.

Ahora, demostremos cómo funciona la eliminación en cascada. Actualmente, tenemos los siguientes registros en la tabla de empleados:

ID de empleado	apellido	nombre	Número de departamento
10000	Herrero	John	30
10001	anderson	dave	999

Ahora eliminemos un registro de la tabla de departamentos y veamos qué sucede:

DELETE FROM departments
WHERE department_id = 30;

Incluso si elimináramos el registro con id_departamento 30 de la tabla de departamentos, la clave externa (usando la eliminación en cascada) eliminaría todos los registros del empleado con id_departamento 30.

Después de las eliminaciones en cascada, la tabla de empleados se ve así:

ID de empleado	apellido	nombre	Número de departamento
10001	anderson	dave	999