SQLite: clave externa establecida en Nulo al eliminar

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 establecida en Nulo al eliminar

Este tutorial de SQLite explica cómo usar Clave externa con “establecer en nulo al eliminar” En SQLite, con sintaxis y ejemplos.

¿Qué es una clave externa “establecida en nulo al eliminar” en SQLite?

Una clave externa con “establecer nulo al eliminar” significa que si se elimina un registro en la tabla principal, el registro correspondiente en la tabla secundaria tiene el campo de clave externa establecido en nulo.Los registros en la tabla secundaria serán No sido borrado

Las claves foráneas que son “nulas al eliminarse” solo se pueden definir en la instrucción CREATE TABLE.

propina: No puede usar ALTER TABLE para agregar una clave externa con “establecer en nulo al eliminar” a una tabla porque SQLite no admite ADD CONSTRAINT en instrucciones ALTER TABLE. Sin embargo, le mostraremos una solución más adelante en este tutorial que le permite agregar una clave externa con “establecer en nulo al eliminar” a una tabla existente.

Cómo crear una clave externa con “establecer Nulo al eliminar” usando la instrucción CREATE TABLE

sintaxis

La sintaxis para crear una clave externa con “establecer en nulo al eliminar” usando la instrucció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 SET NULL
);

ejemplo

Veamos un ejemplo de cómo crear una clave externa con “establecer en nulo al eliminar” 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 SET NULL
);

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.

porque ON DELETE SET NULL, cuando se elimina un registro en la tabla de departamentos, todos los registros correspondientes en la tabla de empleados tienen id_departamento establecido en NULL.

Cómo agregar una clave externa con “establecer en NULL al eliminar” a la tabla existente

No puede agregar claves externas usando “establecer nulo al eliminar” 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 “establecer en nulo al eliminar” 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 SET NULL
);

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 ON DELETE SET NULL 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 SET NULL
);

INSERT INTO employees SELECT * FROM _employees_old;

COMMIT;

PRAGMA foreign_keys=on;

En este ejemplo, creamos una clave externa llamada fk_departments (establecida en nulo al eliminar) que hace referencia a la tabla de departamentos según el campo de departamento_id.

Ahora, vamos a demostrar cómo ON DELETE SET NULL trabajo. 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 eliminamos el registro con id_departamento 30 de la tabla de departamentos, la clave externa (establecida en nulo al eliminar) actualizará la tabla de empleados y cambiará todos los valores de id_departamento con id_departamento 30 a NULL.

Después de la eliminación, la tabla de empleados se ve así:

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