Escriptures a la Base de Dades - Errors de Restriccions d'Integritat

Les restriccions d'integritat garanteixen que les dades s'ajusten a un joc anidat de regles.

Les restriccions es comproven automàticament cada vegada que s'executa una sentència DML que pot trencar alguna d'aquestes regles.

Si es trenca alguna regla, la taula no es modifica i es retorna un error.

Si la subconsulta no retorna valor, l'UPDATE intentarà posar un valor NULL en un camp definit com a NOT NULL (no permet valors nuls al camp LAST_NAME)

UPDATE copy_employees
SET last_name = (SELECT last_name
                 FROM copy_employees
                 WHERE employee_id = 999)
WHERE employee_id = 101;

ERROR 1048 (23000): Column 'last_name' cannot be null

Quan es comproven les restriccions de PK – FK (Clau Primària – Clau Forana)?

La taula EMPLOYEES té una restricció de clau forana a la columna DEPARTMENT_ID que fa referència a la columna DEPARTMENT_ID de la taula DEPARTMENTS.

Això garanteix que cada empleat pertanyi a algun departament vàlid.

A la taula DEPARTMENTS hi ha el departaments 10 i 20 però no el 15.

Quines rectriccions donaran error?

UPDATE employees
SET department_id = 15
WHERE employee_id = 100;

DELETE FROM departments WHERE department_id = 10;

UPDATE employees
SET department_id = 10
WHERE department_id = 20;

Cal fer les proves sobre les taules originals ja que les còpies no tenen restriccions de clau forana.

FOR UPDATE

Quan executem una sentència SELECT no es bloqueja cap fila, de manera que, si primer fem una SELECT per esbrinar un valor i després volem fer alguna modificació a partir d'aquest valor, algú podria haver-lo modificat entremig i per tant no hauríem d'utilitzar el valor antic, elque ens han donat a nosaltres.

Podem assegurar que ningú modifica cap dada de la nostra SELECT, afegint la clàusula FOR UPDATE al final de la SELECT.

Per a treballar amb la clàusula FOR UPDATE cal, entre comanda i comanda, estar en la mateixa connexió. Això implica que no podem utilitzar cap entorn web; cal utilitzar eines client que no finalitzin la connexió entre sentències; per exemple, la comanda mysql.exe, des del símbol del sistema.

Aquest bloqueig es mantindrà fins a finalitzar la transacció amb un COMMIT o ROLLBACK.

La clàusula FOR UPDATE cal afegir-la al final de la sentència SELECT.

Si la sentència SELECT afecta vàries taules, es bloquejaran totes les files afectades de totes les taules afectades.

SELECT e.employee_id, e.salary, d.department_name
FROM employees e JOIN departments d USING (department_id)
WHERE job_id = 'ST_CLERK' AND location_id = 1500
ORDER BY e.employee_id
FOR UPDATE;

Cal utilitzar aquesta clàusula sempre dins una transacció.

START TRANSACTION;
SELECT e.employee_id, e.salary, d.department_name
FROM employees e JOIN departments d USING (department_id)
WHERE job_id = 'ST_CLERK' AND location_id = 1500
ORDER BY e.employee_id
FOR UPDATE;

Ara les 4 files seleccionades estan bloquejades fins que l'usuari realitzi el COMMIT o ROLLBACK.

Podem utilitzar transaccions o bé posar directament SET AUTOCOMMIT=0; i finalitzar amb COMMITo ROLLBACK

Transaccions

Per aprendre sobre les transaccions podem accedir a