Revisió del 15:19, 20 des 2021

Contingut

1 Introducció
2 T1. Previ: regeneració de les dades de la base de dades HR
3 T2. Modificació de les dades
4 T3. Restrictions: clau primària, clau forànea, NULL, UNIQUE
5 T4. Restrictions en cascada
- 5.1 vestuari: base de dades sense restriccions en cascada
- 5.2 HR: base de dades amb restriccions en cascada

Introducció

És la secció 11 que estem seguint del tutorial:

T1. Previ: regeneració de les dades de la base de dades HR

Dins del docker tenim el script /etc/HR.sql que és l'estructura i les dades de la nostra base de dades. Per executar aquest script i regenerar les dades ho podem fer de dues maneres: des de la consola, i des de dins del mysql

Entrem a la consola del docker:

$ docker exec -it mysql_m02bd /bin/bash
bash-4.4# 

bash-4.4# cd /etc
bash-4.4# ls HR.sql

El primer que hem de fer és entrar amb un editor de text (nano) al script HR.sql, i comentar les dues primeres línies:

bash-4.4# nano HR.sql

#CREATE DATABASE HR CHARACTER SET utf8 COLLATE utf8_general_ci;
#use HR;

NOTA: el caràcter coixinet (#) és un comentari.

Mira quin és el contingut del script: veuràs fàcilment tots els creates i els inserts.

I ara ja podem executar el script:

# mysql -u alumne -pkeiL2lai HR -f < /etc/HR.sql > /etc/control_errors.log 2>&1

# cat control_errors.log

La segona manera de fer-ho:

$ docker exec -it mysql_m02bd mysql -u alumne -pkeiL2lai HR

mysql> \. /etc/HR.sql
o bé
mysql> source /etc/HR.sql

Per tant, podem potinejar amb les dades tant com vulguem, que sempre podrem regenerar la base de dades. I com a última solució, recorda que sempre et pots tornar a descarregar el docker.

T2. Modificació de les dades

Insert

INSERT INTO dependents( first_name, last_name, relationship, employee_id) VALUES ( 'Dustin', 'Johnson', 'Child', 178 );

# comprovació:
SELECT * FROM dependents WHERE employee_id = 178;

INSERT INTO dependents( first_name, last_name, relationship, employee_id) VALUES 
( 'Cameron', 'Bell', 'Child', 192 ), 
( 'Michelle', 'Bell', 'Child', 192 );

SELECT * FROM dependents WHERE employee_id = 192;

Per fer aquestes comandes, els usuaris 178 i 192 han d'existir en la taula employees. Si no donaria un error de clau forània (veure més endavant quan parlem de restriccions).

Podem copiar una taula sencera dins d'una altra taula. Per tal de fer-ho necessitem la taula dependents_archive que tingui la mateixa estructura que dependents.

create table dependents_archive(
dependent_id int,
first_name varchar(50),
last_name varchar(50),
relationship varchar(50),
employee_id int
);

I ara ja podem omplir tota la taula, que funcionaria com una còpia de seguretat:

INSERT INTO dependents_archive SELECT * FROM dependents;
Query OK, 33 rows affected (0.02 sec)
Records: 33  Duplicates: 0  Warnings: 0

Update

UPDATE employees SET last_name = 'Lopez' WHERE employee_id = 192;

# comprovació:
SELECT employee_id, first_name, last_name FROM employees WHERE employee_id = 192;

UPDATE dependents SET last_name = 'Lopez' WHERE employee_id = 192;

# fem un update amb una subquery

UPDATE dependents SET last_name =
( SELECT last_name FROM employees WHERE employee_id = dependents.employee_id);

Delete

# comprovació:
SELECT count(*) FROM dependents WHERE dependent_id = 16;

DELETE FROM dependents WHERE dependent_id = 16;

# comprovació:
SELECT count(*) FROM dependents WHERE dependent_id = 16;


DELETE FROM dependents WHERE employee_id IN(100 , 101, 102);

Tenim un empleat amb id=192, i dos fills d'aquest empleat. Primer esborrem l'empleat:

DELETE FROM employees WHERE employee_id = 192;

Si ara borrem els seus fills, veurem que surt: 0 rows affected. No s'han esborrat, perquè ja s'havien esborrat quan hem esborrat el pare. D'aquesta manera es manté la integritat de les dades.

DELETE FROM dependents WHERE employee_id = 192;

T3. Restrictions: clau primària, clau forànea, NULL, UNIQUE

Restricció de clau primària

De la base de dades HR: fixem-nos amb la definició de la taula jobs:

CREATE TABLE jobs (
	job_id INT (11) AUTO_INCREMENT PRIMARY KEY,
	job_title VARCHAR (35) NOT NULL,
	min_salary DECIMAL (8, 2) DEFAULT NULL,
	max_salary DECIMAL (8, 2) DEFAULT NULL
);

insert into jobs values(1,'programador', 2000, 5000);
ERROR 1062 (23000): Duplicate entry '1' for key 'jobs.PRIMARY'

insert into jobs values(20,'programador', 2000, 5000);
Query OK, 1 row affected (0.01 sec)

Restricció de clau forànea

De la base de dades HR: fixem-nos amb la definició de la taula employees:

CREATE TABLE employees (
	employee_id INT (11) AUTO_INCREMENT PRIMARY KEY,
	first_name VARCHAR (20) DEFAULT NULL,
	last_name VARCHAR (25) NOT NULL,
	email VARCHAR (100) NOT NULL,
	phone_number VARCHAR (20) DEFAULT NULL,
	hire_date DATE NOT NULL,
	job_id INT (11) NOT NULL,
	salary DECIMAL (8, 2) NOT NULL,
	manager_id INT (11) DEFAULT NULL,
	department_id INT (11) DEFAULT NULL,
	FOREIGN KEY (job_id) REFERENCES jobs (job_id) ON DELETE CASCADE ON UPDATE CASCADE,
	FOREIGN KEY (department_id) REFERENCES departments (department_id) ON DELETE CASCADE ON UPDATE CASCADE,
	FOREIGN KEY (manager_id) REFERENCES employees (employee_id)
);

mysql> select employee_id,first_name,job_id from employees;
...
|         206 | William     |      1 
...

mysql> update employees set job_id=20 where employee_id=206;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update employees set job_id=25 where employee_id=206;
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`HR`.`employees`, CONSTRAINT `employees_ibfk_1` FOREIGN KEY (`job_id`) REFERENCES `jobs` (`job_id`) ON DELETE CASCADE ON UPDATE CASCADE)

mysql> update employees set job_id=1 where employee_id=206;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Restricció de NULL

De la taula job, està clar que la clau primaria és job_id. Si ens fixem en la definició de la taula, el job_title no pot ser NUL, i en canvi els salaris (màxim i mínim) sí que poden ser nuls.

mysql> update jobs set max_salary=6000 where job_id=20;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update jobs set min_salary=NULL where job_id=20;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update jobs set job_title=NULL where job_id=20;
ERROR 1048 (23000): Column 'job_title' cannot be null

Restricció de UNIQUE

Per trobar una restricció UNIQUE hem d'anar a la base de dades sakila. En el script sakila-schema.sql trobem la definició de la taula store:

CREATE TABLE store (
  store_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
  manager_staff_id TINYINT UNSIGNED NOT NULL,
  address_id SMALLINT UNSIGNED NOT NULL,
  last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY  (store_id),
  UNIQUE KEY idx_unique_manager (manager_staff_id),
  KEY idx_fk_address_id (address_id),
  CONSTRAINT fk_store_staff FOREIGN KEY (manager_staff_id) REFERENCES staff (staff_id) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT fk_store_address FOREIGN KEY (address_id) REFERENCES address (address_id) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Fixem-nos en les dues línies:

...
  manager_staff_id TINYINT UNSIGNED NOT NULL,
...
  UNIQUE KEY idx_unique_manager (manager_staff_id)
...

S'hagués pogut posar de forma més simplificada en una sola línia:

  manager_staff_id TINYINT UNSIGNED NOT NULL UNIQUE,

Aquesta restricció ens està dient que el manager_staff_id no es pot repetir en aquesta taula. És a dir, que no pot haver-hi dues botigues amb el mateix manàger.

Anem a fer saltar aquesta restricció:

Només hi ha dues botigues i només hi ha dues persones en el staff:

mysql> select store_id, manager_staff_id from store;
+----------+------------------+
| store_id | manager_staff_id |
+----------+------------------+
|        1 |                1 |
|        2 |                2 |
+----------+------------------+

mysql> select staff_id, first_name from staff;
+----------+------------+
| staff_id | first_name |
+----------+------------+
|        1 | Mike       |
|        2 | Jon        |
+----------+------------+

No podem fer que el manager_staff_id=1 sigui el manager de la botiga 1:

mysql> update store set manager_staff_id=1 where store_id=2;
ERROR 1062 (23000): Duplicate entry '1' for key 'store.idx_unique_manager'

Restricció de CHECK

https://www.mysqltutorial.org/mysql-check-constraint/

Treballem amb la base de dades HR, i creem la taula parts amb 4 restriccions tipus CHECK:

DROP TABLE IF EXISTS parts;

CREATE TABLE parts (
    part_no VARCHAR(18) PRIMARY KEY,
    description VARCHAR(40),
	type VARCHAR(15) NULL CHECK (type IN ('hardware','software','electronics')),
    cost DECIMAL(10,2) NOT NULL CHECK (cost >= 0),
    price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
	CONSTRAINT parts_chk_price_gt_cost CHECK(price >= cost)
);

Veiem que hi ha 4 restriccions:

type pot ser un d'aquests valors: hardware,software,electronics
cost > 0 (ha de ser un valor positiu)
price > 0 (ha de ser un valor positiu)
una altra restricció: el preu ha de ser més gran que el cost (price >= cost)

mysql> desc parts;
+-------------+---------------+------+-----+---------+-------+
| Field       | Type          | Null | Key | Default | Extra |
+-------------+---------------+------+-----+---------+-------+
| part_no     | varchar(18)   | NO   | PRI | NULL    |       |
| description | varchar(40)   | YES  |     | NULL    |       |
| type        | varchar(15)   | YES  |     | NULL    |       |
| cost        | decimal(10,2) | NO   |     | NULL    |       |
| price       | decimal(10,2) | NO   |     | NULL    |       |
+-------------+---------------+------+-----+---------+-------+

o bé:

mysql> SHOW CREATE TABLE parts;

...
CREATE TABLE `parts` (
  `part_no` varchar(18) NOT NULL,
  `description` varchar(40) DEFAULT NULL,
  `type` varchar(15) DEFAULT NULL,
  `cost` decimal(10,2) NOT NULL,
  `price` decimal(10,2) NOT NULL,
  PRIMARY KEY (`part_no`),
  CONSTRAINT `parts_chk_1` CHECK ((`type` in (_utf8mb4'hardware',_utf8mb4'software',_utf8mb4'electronics'))),
  CONSTRAINT `parts_chk_2` CHECK ((`cost` >= 0)),
  CONSTRAINT `parts_chk_3` CHECK ((`price` >= 0)),
  CONSTRAINT `parts_chk_price_gt_cost` CHECK ((`price` >= `cost`))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 |
...

Veiem com es violen aquestes restriccions:

mysql> INSERT INTO parts(part_no, description,cost,price) VALUES('A-001','Cooler',200,100);
ERROR 3819 (HY000): Check constraint 'parts_chk_price_gt_cost' is violated.

mysql> INSERT INTO parts(part_no, description,cost,price) VALUES('A-001','Cooler',100,-200);
ERROR 3819 (HY000): Check constraint 'parts_chk_3' is violated.

mysql> INSERT INTO parts(part_no, description,cost,price) VALUES('A-001','Cooler',-100,200);
ERROR 3819 (HY000): Check constraint 'parts_chk_2' is violated.

mysql> INSERT INTO parts(part_no, description,type,cost,price) VALUES('A-001','Cooler','tipus no vàlid',100,200);
ERROR 3819 (HY000): Check constraint 'parts_chk_1' is violated.

I finalment una inserció correcta:

mysql> INSERT INTO parts(part_no, description,type,cost,price) VALUES('A-001','Cooler','hardware',100,200);

mysql> select * from parts;
+---------+-------------+----------+--------+--------+
| part_no | description | type     | cost   | price  |
+---------+-------------+----------+--------+--------+
| A-001   | Cooler      | hardware | 100.00 | 200.00 |
+---------+-------------+----------+--------+--------+

En la base de dades vestuari (script vestuari_v5.sql) tenim un exemple de restricció CHECK:

CREATE TABLE LOCALITZACIO (
	id_loc smallint PRIMARY KEY,
	loc VARCHAR(50) NOT NULL,
	tipus VARCHAR(20) NULL CHECK (TIPUS IN ('armari', 'capsa', 'guarda-roba', 'penjador', 'sabater', 'altell')),
	hab CHAR(5) NOT NULL CHECK (hab IN ('HAB-1','HAB-2','HAB-3','HAB-4','HAB-5')),
	notes VARCHAR(255) NULL
);

T4. Restrictions en cascada

vestuari: base de dades sense restriccions en cascada

Fixa't en aquestes dues taules que pots veure en el script /etc/vestuari.sql:

#OBRA (id_obra, obra, year, notes)
CREATE TABLE OBRA (
	id_obra smallint PRIMARY KEY,
	obra VARCHAR(50) NOT NULL,
	year SMALLINT NOT NULL,
	notes VARCHAR(255)
);

#PERSONATGE (id_pers, pers, rol, id_obra (FK))
CREATE TABLE PERSONATGE (
	id_pers smallint PRIMARY KEY,
	pers VARCHAR(50) NOT NULL,
	rol VARCHAR(50) NULL,
	id_obra SMALLINT NOT NULL,
	FOREIGN KEY (id_obra) REFERENCES OBRA(id_obra)
);

Miro quines obres tenim:

mysql> select * from OBRA;
+---------+----------------------------+------+---------------------------------------------+
| id_obra | obra                       | year | notes                                       |
+---------+----------------------------+------+---------------------------------------------+
|       1 | La Caputxeta Vermella 2018 | 2018 | versió clàssica                             |
|       2 | La Caputxeta Vermella 2021 | 2021 | versió moderna i ambientada a l'actualitat  |
+---------+----------------------------+------+---------------------------------------------+

Vull eliminar tota referència a l'obra 1. Però haurem de mantenir la integritat:

DELETE FROM OBRA WHERE id_obra=1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`vestuari`.`PERSONATGE`, CONSTRAINT `PERSONATGE_ibfk_1` FOREIGN KEY (`id_obra`) REFERENCES `OBRA` (`id_obra`))

No ho podem fer perquè tenim personatges en aquesta obra. Hem d'eliminar el vestuari d'aquesta obra:

DELETE FROM PERSONATGE WHERE id_obra=1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`vestuari`.`VEST_PERS`, CONSTRAINT `VEST_PERS_ibfk_2` FOREIGN KEY (`id_pers`) REFERENCES `PERSONATGE` (`id_pers`))

No ho podem fer perquè aquests personatges estan referenciats a la taula VEST_PERS. I alhora, VEST_PERS fa referència a la taula vestuari. Per tant, ho hauré de fer manualment, i per ordre.

Quines són les referències del vestuari de l'obra id_obra=1?

SELECT p.id_obra, ref FROM PERSONATGE p
INNER JOIN VEST_PERS USING(id_pers)
WHERE p.id_obra=1;

+---------+-----+
| id_obra | ref |
+---------+-----+
|       1 |   1 |
|       1 |   2 |
|       1 |   3 |
|       1 |   4 |
|       1 |   5 |
|       1 |   6 |
|       1 |   7 |
|       1 |   8 |
|       1 |   9 |
|       1 |  10 |
|       1 |  11 |
|       1 |  12 |
|       1 |  13 |
|       1 |  14 |
|       1 |  15 |
|       1 |  16 |
|       1 |  17 |
|       1 |  18 |
|       1 |  19 |
+---------+-----+

I per tant,

DELETE FROM VEST_PERS WHERE ref>=1 AND ref<=19;
Query OK, 19 rows affected (0.01 sec)


DELETE FROM PERSONATGE WHERE id_obra=1;
Query OK, 5 rows affected (0.02 sec)

(ara sí que funciona)

DELETE FROM OBRA WHERE id_obra=1;
Query OK, 1 row affected (0.01 sec)

(ara sí que funciona)

Fixem-nos que el vestuari que feia servir en l'obra 1 no l'he d'eliminar (aquest vestuari es podia fer servir en d'altres obres).

Per tant, si vull eliminar totes les dades de la base de dades, ho hauré de fer manualment i per ordre:

DROP TABLE IF EXISTS VEST_PERS;
DROP TABLE IF EXISTS VESTUARI;
DROP TABLE IF EXISTS PERSONATGE;
DROP TABLE IF EXISTS OBRA;

DROP TABLE IF EXISTS LOCALITZACIO;
DROP TABLE IF EXISTS AMBIENTACIO;
DROP TABLE IF EXISTS CATEGORIA;

Per exemple, no podria començar per CATEGORIA si hi ha obres que depenen d'aquesta categoria. No puc esborrar obra si hi ha personatges que depenen de l'obra. No puc esborrar personatge si hi ha vesturi que està associat al personatge.

Per tant, la primera taula que s'ha d'esborrar és VEST_PERS, que desassocia VESTUARI i PERSONTAGE.

Per fer tot això de forma automàtica tenim les opcions en 'CASCADE'. Però s'ha d'anar en compte perquè com el seu nom inidica fa els esborrats en cascada: si esborro una obra, esborrarà tots els personatges d'aquella obra, esborrarà a la taula VEST_PERS tota referència al personatge.

HR: base de dades amb restriccions en cascada

Fixa't en aquestes dues taules que pots veure en el script /etc/HR.sql:

CREATE TABLE employees (
	employee_id INT (11) AUTO_INCREMENT PRIMARY KEY,
	first_name VARCHAR (20) DEFAULT NULL,
	last_name VARCHAR (25) NOT NULL,
	email VARCHAR (100) NOT NULL,
	phone_number VARCHAR (20) DEFAULT NULL,
	hire_date DATE NOT NULL,
	job_id INT (11) NOT NULL,
	salary DECIMAL (8, 2) NOT NULL,
	manager_id INT (11) DEFAULT NULL,
	department_id INT (11) DEFAULT NULL,
	FOREIGN KEY (job_id) REFERENCES jobs (job_id) ON DELETE CASCADE ON UPDATE CASCADE,
	FOREIGN KEY (department_id) REFERENCES departments (department_id) ON DELETE CASCADE ON UPDATE CASCADE,
	FOREIGN KEY (manager_id) REFERENCES employees (employee_id)
);

CREATE TABLE dependents (
	dependent_id INT (11) AUTO_INCREMENT PRIMARY KEY,
	first_name VARCHAR (50) NOT NULL,
	last_name VARCHAR (50) NOT NULL,
	relationship VARCHAR (25) NOT NULL,
	employee_id INT (11) NOT NULL,
	FOREIGN KEY (employee_id) REFERENCES employees (employee_id) ON DELETE CASCADE ON UPDATE CASCADE
);

Anem a veure els empleats i els seus depenents (amb una inner join):

select e.employee_id, e.first_name, e.last_name, d.dependent_id, d.first_name, d.last_name, d.employee_id relationship  FROM
employees e INNER JOIN dependents d USING (employee_id)
order by e.employee_id;

...
|         203 | Susan       | Mavris     |           13 | Uma        | Mavris     |          203 |
|         204 | Hermann     | Baer       |           21 | Kirsten    | Baer       |          204 |
|         205 | Shelley     | Higgins    |            2 | Nick       | Higgins    |          205 |
|         206 | William     | Gietz      |            1 | Penelope   | Gietz      |          206 |
...

Veiem que la gent només té un fill. No passa res.

Puc canviar el id de l'empleat 206 (a 207), i també es canviarà a dependents, de manera que la integritat de la relació encara es mantindrà.
Puc eliminar l'empleat 205 o el 204, i també s'eliminarà el seu fill, de manera que la integritat de la relació encara es mantindrà.

update employees set employee_id=207 where employee_id=206;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

delete from employees where employee_id=205;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`HR`.`employees`, CONSTRAINT `employees_ibfk_3` FOREIGN KEY (`manager_id`) REFERENCES `employees` (`employee_id`))

No puc eliminar aquest empleat perquè precisament és manager d'altres empleats, i em salataria la integritat.

mysql> delete from employees where employee_id=204;
Query OK, 1 row affected (0.01 sec)

Tornem a fer la select:

|         203 | Susan       | Mavris     |           13 | Uma        | Mavris     |          203 |
|         205 | Shelley     | Higgins    |            2 | Nick       | Higgins    |          205 |
|         207 | William     | Gietz      |            1 | Penelope   | Gietz      |          207 |

Veiem com el empleat 204 no existeix (i també ha desaparegut el seu fill, el 21). I veiem també com l'empleat 206 ha passat a ser el 207 i la seva filla Penelope continua estan ben relacionada.

Per tant, les dades es poden actualitzar en cascada si així ho hem definit en les taules.

creat per Joan Quintana Compte, desembre 2021

Diferència entre revisions de la pàgina «Modificant les dades: insert, update, delete»

Revisió del 15:19, 20 des 2021

Contingut

Introducció

T1. Previ: regeneració de les dades de la base de dades HR

T2. Modificació de les dades

Insert

Update

Delete

T3. Restrictions: clau primària, clau forànea, NULL, UNIQUE

Restricció de clau primària

Restricció de clau forànea

Restricció de NULL

Restricció de UNIQUE

Restricció de CHECK

T4. Restrictions en cascada

vestuari: base de dades sense restriccions en cascada

HR: base de dades amb restriccions en cascada

Menú de navegació

Eines de l'usuari

Espais de noms

Variants

Vistes

Més

Cerca

Navegació

Institut Jaume Balmes

Màquines recreatives

CNC

Informàtica musical

joanillo.org Planet

Eines