RESTRICCIÓN: integridad de la base de datos

Control de integridad de la base de datos

Otra cosa importante que debe saber sobre las bases de datos son las RESTRICCIONES. Con la ayuda de las restricciones, puede controlar los cambios de datos en sus tablas y mantener su integridad y consistencia.

¿ Qué es la consistencia de datos cuando hablamos de una base de datos?

Tomemos nuestra tienda en línea con tablas de empleados, productos y tareas . Ya sabemos que puede haber tareas en la tabla de tareas que no estén asignadas a nadie: el employee_id de dichas filas es NULL.

Pero, ¿qué sucede si hay una entrada en la tabla de tareas con un employee_id igual a, digamos, 115? Después de todo, no tenemos tal empleado. No tenemos un empleado con id = 115 en la tabla de empleados. Al mismo tiempo, hay un enlace a un empleado con esta identificación en la tabla de tareas. Este es un ejemplo de inconsistencia de datos .

Entonces, ¿cómo reconciliamos estos datos? Lo ideal sería que el servidor SQL, ante cualquier cambio de datos, controlara todos estos matices. Y existe tal oportunidad, se llama FOREIGN_KEY.

Si alguna columna en su tabla contiene no solo números, sino también filas de identificación de otra tabla, entonces esto se puede especificar explícitamente.

Adición de una CLAVE EXTRANJERA

Dicha clave se puede agregar a la tabla tanto en la etapa de su creación como después, usando ALTER TABLE. El formato no es fundamentalmente diferente. Presentaremos ambas opciones.

La forma general de tal clave/regla es:

FOREIGN KEY (column)
  	REFERENCES table(column)

Agreguemos esta clave/regla a la tabla de tareas para garantizar que todos los employee_ids de la tabla se refieran a una entrada existente en la tabla de empleados. Este script se verá así:

ALTER TABLE task
      ADD FOREIGN KEY (employee_id)
  	REFERENCES employee(id)

Y si decidiéramos agregar esta regla al momento de crear la tabla de tareas, entonces el código quedaría así:

CREATE TABLE task (
      id INT,
      name VARCHAR(100),
      employee_id INT,
      deadline DATE,
 
      PRIMARY KEY (id),
  	  FOREIGN KEY (employee_id)  
	      REFERENCES employee(id)
);

Por cierto, hay situaciones en las que la cadena a la que nos referimos tiene una clave compuesta única: por ejemplo, "Nombre y año de nacimiento" o "productCatogoryId y productId". Entonces FOREIGN KEY se puede escribir así:

FOREIGN KEY (our_column1, our_column2)
  	REFERENCES table(their_column1, their_column2)

LLAVE EXTRANJERA y cambio de datos

Ahora imagine una situación en la que decidimos actualizar algunos datos en la tabla de empleados y nuestra identificación de empleado ha cambiado. ¿Qué pasará con los datos en la tabla de tareas? Así es, se volverán irrelevantes y se violará la integridad de nuestra base de datos.

Para evitar que esto suceda, puede decirle a SQL Server que cambie el employee_id de todas las filas en todas las tablas que se refieren a este cambio de id en particular cuando cambia el id en la tabla de empleados.

Estos scripts se denominan OnUpdate y OnDelete . ¿Qué hacer si cambia la identificación del registro y qué hacer si se elimina el registro?

Con la eliminación, no todo es tan sencillo. Si tiene objetos dependientes representados por cadenas en la base de datos que se refieren entre sí, es posible una amplia variedad de escenarios de comportamiento al eliminar un objeto.

Digamos que eliminamos un usuario del sitio, lo que significa que debemos eliminar toda su correspondencia personal. Pero es poco probable que eliminemos todos sus comentarios públicos.

O un empleado renuncia. Sería extraño que renunciara y al mismo tiempo todas las tareas que le habían sido asignadas desaparecieran de la base de datos. Pero si hubieran permanecido designados no por él, también habría resultado mal. Es más correcto hacerlo para que el empleado pueda renunciar después de reasignar todas sus tareas a otras personas.

Así es como podemos describir estos escenarios usando FOREIGN KEY. La forma general de tal clave/regla es:

FOREIGN KEY (column)
  	REFERENCES table(column)
 	[ON DELETE reference_option]
 	[ON UPDATE reference_option]

¿Qué hacer en caso de borrar (ON DELETE) o cambiar (ON UPDATE) registros? En total, pueden existir 5 opciones para que el servidor SQL actúe en cada una de estas situaciones:

Así es como podríamos modificar nuestra tabla de tareas:

ALTER TABLE task
  	ADD FOREIGN KEY (employee_id)
  	REFERENCES employee(id)
  	ON UPDATE CASCADE
  	ON DELETE RESTRICT;

Lo que está escrito aquí:

EN CASCADA DE ACTUALIZACIÓN : si la clave de identificación en la tabla de empleados cambia, también cambie el employee_id en la tabla de tareas que hace referencia a ella.

ON DELETE RESTRICT : si se elimina una fila de la tabla de empleados y se hace referencia a ella desde la tabla de tareas, evite que la fila se elimine de la tabla de empleados.