<div class="page">
<div class="cover text-center">
<img class="mx-auto" src=/itb/images/logo_mislata.png alt="logo">
# Modificació i eliminació de registres
<div class="text-end fit-content ms-auto my-3 mt-auto pt-3">
<p><strong>Autor:</strong> Joan Puigcerver Ibáñez</p>
<p><strong>Correu electrònic:</strong> j.puigcerveribanez@edu.gva.es</p>
<p><strong>Curs:</strong> 2023/2024</p>
</div>
<div>
<p class="fw-bold mb-0">Llicència: BY-NC-SA</p>
<p class="d-none d-md-block">(Reconeixement - No Comercial - Compartir Igual)</p>
<a href="https://creativecommons.org/licenses/by-nc-sa/4.0/deed.ca" target="_blank">
<img class="mx-auto" src="/itb/images/license.png" alt="Licence"/>
</a>
</div><!--license-->
</div><!--cover-->
</div><!--page-->
{:toc}
## Introducció
El __Llenguatge de Manipulació de Dades (DML o _Data Definition Language_)__
permet manipular les dades de la base de dades.
El llenguatge implementa les següents sentències:
- `SELECT`: Permet consultar les dades de la base de dades.
- `INSERT`: Permet inserir noves dades a la base de dades.
- `UPDATE`: Permet modificar dades existents.
- `DELETE`: Permet elmininar dades.
En aquest material ens centrarem en les sentències `UPDATE` i `DELETE`,
que permeten modificar les dades dels registres de la base de dades,
o eliminar-los.
A més, veurem com es poden escriure consultes que modifiquen i eliminen
dades de múltiples taules alhora.
## Modificació de dades (`UPDATE`)
La sentència `UPDATE` permet modificar les dades existents
en la base de dades, per modificar els valors
dels camps especificats.
Té el següent format:
```sql
UPDATE table_name
SET col1={expr1|DEFAULT} [,col2={expr2|DEFAULT}] ...
[WHERE where_condition]
[ORDER BY ...]
```
- `UPDATE`: Indica que volem modificar registres en la base de dades.
- `table_name`: Especifica la taula d'on es modificaran les dades.
- `SET`: Indica els valos que seran modificats.
- `col1={expr1|DEFAULT}`: Indica la columna que es modifica i el valor que rebrà:
- `col1` fa referència al nom de la columna.
- `expr1` fa referència a una expresió per indicar un valor.
- `DEFAULT` fa referència al valor per defecte de la columna.
- `[, col2={expr2|DEFAULT}] ...`: Es poden indicar modificacions sobre més d'una
columna en la mateixa sentència, separades per comes.
- `[WHERE where_condition]`: Condició per especificar quins registres seran modificats.
- `[ORDER BY ...]`: Indica l'ordre en el que es realitzaran les modificacions.
::: docs
Documentació oficial sobre modificació de registres:
- MariaDB: https://mariadb.com/kb/en/update/
- MySQL 8.0: https://dev.mysql.com/doc/refman/8.0/en/update.html
:::
:::: info "Dades d'exemple"
::: danger
Tots els exemples parteixen a partir de l'estat inicial de la base de dades
definit en el següent schema.
:::
Els següents exemples utilitzaran les següents dades:
```sql
CREATE TABLE persona (
dni CHAR(9) PRIMARY KEY,
nom VARCHAR(50) NOT NULL
);
CREATE TABLE cotxe (
matricula CHAR(7) PRIMARY KEY,
n_bastidor CHAR(8) UNIQUE NOT NULL,
marca VARCHAR(50) NOT NULL,
model VARCHAR(50) NOT NULL,
km INT UNSIGNED NOT NULL DEFAULT 0,
dni_propietari CHAR(9),
data_compra DATE NULL,
FOREIGN KEY (dni_propietari) REFERENCES persona (dni)
);
INSERT INTO persona(dni, nom) VALUES
("01727468F", "Pere"),
("01780605Z", "Marta"),
("01809637C", "Carla"),
("02064836B", "Daniel"),
("02827316H", "Laura");
INSERT INTO cotxe (matricula, n_bastidor, marca, model, km, data_compra, dni_propietari) VALUES
("4231KJL", "16483920", "Ford", "Focus", 39031, "2000-12-08", "01727468F"),
("4078CMP", "23948756", "Ford", "Fiesta", 10542, "2001-01-02", "02827316H"),
("3091LSF", "12347003", "Volkswagen", "Golf", 8065, "2001-04-02", "02064836B"),
("7012DLM", "84012398", "Peugeot", "207", 48623, "2001-11-28", "01809637C"),
("3877FFT", "74832134", "Audi", "Q2", 57737, "2001-12-05", "01809637C"),
("1034KMC", "56481093", "BMW", "X3", 8480, NULL, NULL);
SELECT * FROM cotxe;
+-----------+------------+------------+--------+-------+----------------+-------------+
| matricula | n_bastidor | marca | model | km | dni_propietari | data_compra |
+-----------+------------+------------+--------+-------+----------------+-------------+
| 1034KMC | 56481093 | BMW | X3 | 8480 | NULL | NULL |
| 3091LSF | 12347003 | Volkswagen | Golf | 8065 | 02064836B | 2001-04-02 |
| 3877FFT | 74832134 | Audi | Q2 | 57737 | 01809637C | 2001-12-05 |
| 4078CMP | 23948756 | Ford | Fiesta | 10542 | 02827316H | 2001-01-02 |
| 4231KJL | 16483920 | Ford | Focus | 39031 | 01727468F | 2000-12-08 |
| 7012DLM | 84012398 | Peugeot | 207 | 48623 | 01809637C | 2001-11-28 |
+-----------+------------+------------+--------+-------+----------------+-------------+
```
::::
### Modificació a un valor concret
En les expresions sentència `UPDATE` els camps poden ser modificats per valors concrets
indicats en la consulta.
::: example "UPDATE amb valors concrets"
En aquest cas, modifiquem el cotxe '1034KMC' i establim la data de compra al valor '2024-01-08'
i el propietari a '01780605Z'.
```sql
UPDATE cotxe
SET data_compra = '2024-01-08', dni_propietari = '01780605Z'
WHERE matricula = '1034KMC';
SELECT * FROM cotxe;
+-----------+------------+------------+--------+-------+----------------+-------------+
| matricula | n_bastidor | marca | model | km | dni_propietari | data_compra |
+-----------+------------+------------+--------+-------+----------------+-------------+
| 1034KMC | 56481093 | BMW | X3 | 8480 | 01780605Z | 2024-01-08 |
| 3091LSF | 12347003 | Volkswagen | Golf | 8065 | 02064836B | 2001-04-02 |
| 3877FFT | 74832134 | Audi | Q2 | 57737 | 01809637C | 2001-12-05 |
| 4078CMP | 23948756 | Ford | Fiesta | 10542 | 02827316H | 2001-01-02 |
| 4231KJL | 16483920 | Ford | Focus | 39031 | 01727468F | 2000-12-08 |
| 7012DLM | 84012398 | Peugeot | 207 | 48623 | 01809637C | 2001-11-28 |
+-----------+------------+------------+--------+-------+----------------+-------------+
```
:::
### Modificació al valor per defecte
Si un camp té definit un valor per defecte, podem establir el camp a aquest valor
mitjançant la paraula clau `DEFAULT`.
::: example "UPDATE amb valors concrets"
En aquest cas, modifiquem el cotxe '1034KMC' i establim els quilòmetres
al valor per defecte.
```sql
UPDATE cotxe
SET km = DEFAULT
WHERE matricula = '1034KMC';
SELECT * FROM cotxe;
+-----------+------------+------------+--------+-------+----------------+-------------+
| matricula | n_bastidor | marca | model | km | dni_propietari | data_compra |
+-----------+------------+------------+--------+-------+----------------+-------------+
| 1034KMC | 56481093 | BMW | X3 | 0 | 01780605Z | 2024-01-08 |
| 3091LSF | 12347003 | Volkswagen | Golf | 8065 | 02064836B | 2001-04-02 |
| 3877FFT | 74832134 | Audi | Q2 | 57737 | 01809637C | 2001-12-05 |
| 4078CMP | 23948756 | Ford | Fiesta | 10542 | 02827316H | 2001-01-02 |
| 4231KJL | 16483920 | Ford | Focus | 39031 | 01727468F | 2000-12-08 |
| 7012DLM | 84012398 | Peugeot | 207 | 48623 | 01809637C | 2001-11-28 |
+-----------+------------+------------+--------+-------+----------------+-------------+
```
:::
### Modificació a partir d'un altre valor
Es poden modificar valors a partir de valors existents en la base de dades.
En aquest cas, s'ha d'utilitzar el nom del camp en l'expressió `SET`.
::: example "UPDATE a partir d'un altre valor"
En aquest cas, modifiquem els quilòmetres del cotxe '1034KMC' per sumar-li 10km més.
```sql
UPDATE cotxe
SET km = km + 10
WHERE matricula = '1034KMC';
SELECT * FROM cotxe;
+-----------+------------+------------+--------+-------+----------------+-------------+
| matricula | n_bastidor | marca | model | km | dni_propietari | data_compra |
+-----------+------------+------------+--------+-------+----------------+-------------+
| 1034KMC | 56481093 | BMW | X3 | 8490 | 01780605Z | 2024-01-08 |
| 3091LSF | 12347003 | Volkswagen | Golf | 8065 | 02064836B | 2001-04-02 |
| 3877FFT | 74832134 | Audi | Q2 | 57737 | 01809637C | 2001-12-05 |
| 4078CMP | 23948756 | Ford | Fiesta | 10542 | 02827316H | 2001-01-02 |
| 4231KJL | 16483920 | Ford | Focus | 39031 | 01727468F | 2000-12-08 |
| 7012DLM | 84012398 | Peugeot | 207 | 48623 | 01809637C | 2001-11-28 |
+-----------+------------+------------+--------+-------+----------------+-------------+
```
:::
### Modificació múltiples registres
Es poden realitzar consultes que modifiquen múltiples registres d'una taula.
Els registres que es modificaran s'especifiquen en la condició `WHERE`.
::: example "UPDATE múltiples registres"
En aquest cas, modifiquem els quilòmetres del cotxes 'Ford' per augmentar en
100 els quilòmetres.
```sql
UPDATE cotxe
SET km = km + 100
WHERE marca = 'Ford';
SELECT * FROM cotxe;
+-----------+------------+------------+--------+-------+----------------+-------------+
| matricula | n_bastidor | marca | model | km | dni_propietari | data_compra |
+-----------+------------+------------+--------+-------+----------------+-------------+
| 1034KMC | 56481093 | BMW | X3 | 8490 | 01780605Z | 2024-01-08 |
| 3091LSF | 12347003 | Volkswagen | Golf | 8065 | 02064836B | 2001-04-02 |
| 3877FFT | 74832134 | Audi | Q2 | 57737 | 01809637C | 2001-12-05 |
| 4078CMP | 23948756 | Ford | Fiesta | 10642 | 02827316H | 2001-01-02 |
| 4231KJL | 16483920 | Ford | Focus | 39131 | 01727468F | 2000-12-08 |
| 7012DLM | 84012398 | Peugeot | 207 | 48623 | 01809637C | 2001-11-28 |
+-----------+------------+------------+--------+-------+----------------+-------------+
```
:::
### Modificació segons valors d'una altra taula
De la mateixa manera que en la sentència `SELECT`,
en la sentència `UPDATE` es poden utilitzar clàusules `JOIN`
per consultar valors d'altres taules.
::: example "UPDATE segons valors d'una altra taula"
En aquest cas, modifiquem els quilòmetres del cotxes que pertanyen
a la persona amb nom 'Carla' i augmentem en 1000 els quilòmetres.
```sql
UPDATE cotxe c
INNER JOIN persona p
ON c.dni_propietari = p.dni
SET c.km = c.km + 1000
WHERE p.nom = 'Carla';
SELECT * FROM cotxe;
+-----------+------------+------------+--------+-------+----------------+-------------+
| matricula | n_bastidor | marca | model | km | dni_propietari | data_compra |
+-----------+------------+------------+--------+-------+----------------+-------------+
| 1034KMC | 56481093 | BMW | X3 | 8490 | 01780605Z | 2024-01-08 |
| 3091LSF | 12347003 | Volkswagen | Golf | 8065 | 02064836B | 2001-04-02 |
| 3877FFT | 74832134 | Audi | Q2 | 58737 | 01809637C | 2001-12-05 |
| 4078CMP | 23948756 | Ford | Fiesta | 10642 | 02827316H | 2001-01-02 |
| 4231KJL | 16483920 | Ford | Focus | 39131 | 01727468F | 2000-12-08 |
| 7012DLM | 84012398 | Peugeot | 207 | 49623 | 01809637C | 2001-11-28 |
+-----------+------------+------------+--------+-------+----------------+-------------+
```
:::
### Modificació múltiples taules
La sentència `UPDATE` pot ser utilitza per modificar valors
de múltiples taules alhora mitjançant clàusules `JOIN`.
:::: example "UPDATE segons valors d'una altra taula"
En aquest cas, modifiquem el DNI de la persona 'Pere'
a '80594552E'. El modifiquem també de la taula `cotxe`.
::: danger
En aquest cas, estem modificant la clau primària de la taula `persona`
referenciada en la clau forana de la taula `cotxe`.
Aquesta sentència falla ja que:
- Si modifiquem primer la clau primària de `persona`, la clau forana de `cotxe` fa referència a una persona que no existeix.
- Si modifiquem primer la clau forana de `cotxe`, el nou valor no pertany a cap persona.
El SGBD no deixa executar consultes que ens puguen portar a errors d'integritat de les dades, per tant,
no deixa executar la consulta.
Per "botar-nos" aquestes restriccions, les podem deshabilitar temporalment i tornar-les a habilitar amb:
```sql
SET FOREIGN_KEY_CHECKS=0; -- Desactiva les comprovacions d'integritat de les claus foranes
SET FOREIGN_KEY_CHECKS=1; -- Activa les comprovacions d'integritat de les claus foranes
```
:::
```sql
SET FOREIGN_KEY_CHECKS=0;
UPDATE persona p
INNER JOIN cotxe c
ON c.dni_propietari = p.dni
SET c.dni_propietari = '80594552E', p.dni = '80594552E'
WHERE p.nom = 'Pere';
SET FOREIGN_KEY_CHECKS=1;
SELECT * FROM persona;
+-----------+--------+
| dni | nom |
+-----------+--------+
| 01780605Z | Marta |
| 01809637C | Carla |
| 02064836B | Daniel |
| 02827316H | Laura |
| 80594552E | Pere |
+-----------+--------+
SELECT * FROM cotxe;
+-----------+------------+------------+--------+-------+----------------+-------------+
| matricula | n_bastidor | marca | model | km | dni_propietari | data_compra |
+-----------+------------+------------+--------+-------+----------------+-------------+
| 1034KMC | 56481093 | BMW | X3 | 8490 | 01780605Z | 2024-01-08 |
| 3091LSF | 12347003 | Volkswagen | Golf | 8065 | 02064836B | 2001-04-02 |
| 3877FFT | 74832134 | Audi | Q2 | 58737 | 01809637C | 2001-12-05 |
| 4078CMP | 23948756 | Ford | Fiesta | 10642 | 02827316H | 2001-01-02 |
| 4231KJL | 16483920 | Ford | Focus | 39131 | 80594552E | 2000-12-08 |
| 7012DLM | 84012398 | Peugeot | 207 | 49623 | 01809637C | 2001-11-28 |
+-----------+------------+------------+--------+-------+----------------+-------------+
```
::::
## Eliminació de registres (`DELETE`)
La sentència `DELETE` permet eliminar registres existents
en la base de dades.
Té el següent format:
```sql
DELETE [table_name[, table_name2] ...]
FROM table_name [, table_name2] ...
[WHERE where_condition]
[ORDER BY ...]
```
- `DELETE`: Indica que volem eliminar registres en la base de dades.
- `[table_name [, table_name2] ...]`: Especifica les taules d'on s'eliminaran les dades. Si no s'especifica, s'utilitzaran les mateixes que definides en `FROM`.
- `FROM table_name [, table_name2] ...`: Especifica les taules d'on es consultaran les dades per establir les condicions
de la eliminació.
- `[WHERE where_condition]`: Condició per especificar quins registres seran eliminats.
- `[ORDER BY ...]`: Indica l'ordre en el que es realitzaran les eliminacions.
::: docs
Documentació oficial sobre eliminació de registres:
- MariaDB: https://mariadb.com/kb/en/delete/
- MySQL 8.0: https://dev.mysql.com/doc/refman/8.0/en/delete.html
:::
### Eliminació de registres d'una única taula
La manera més senzilla d'eliminar dades és mitjançant
la sentència `DELETE` que afecte a una única taula.
::: example "DELETE amb una taula"
Eliminem els cotxes de la marca 'Ford'.
```sql
SELECT * FROM cotxe;
+-----------+------------+------------+--------+-------+----------------+-------------+
| matricula | n_bastidor | marca | model | km | dni_propietari | data_compra |
+-----------+------------+------------+--------+-------+----------------+-------------+
| 1034KMC | 56481093 | BMW | X3 | 8490 | 01780605Z | 2024-01-08 |
| 3091LSF | 12347003 | Volkswagen | Golf | 8065 | 02064836B | 2001-04-02 |
| 3877FFT | 74832134 | Audi | Q2 | 58737 | 01809637C | 2001-12-05 |
| 4078CMP | 23948756 | Ford | Fiesta | 10642 | 02827316H | 2001-01-02 |
| 4231KJL | 16483920 | Ford | Focus | 39131 | 80594552E | 2000-12-08 |
| 7012DLM | 84012398 | Peugeot | 207 | 49623 | 01809637C | 2001-11-28 |
+-----------+------------+------------+--------+-------+----------------+-------------+
DELETE FROM cotxe
WHERE marca = "Ford";
SELECT * FROM cotxe;
+-----------+------------+------------+--------+-------+----------------+-------------+
| matricula | n_bastidor | marca | model | km | dni_propietari | data_compra |
+-----------+------------+------------+--------+-------+----------------+-------------+
| 1034KMC | 56481093 | BMW | X3 | 8490 | 01780605Z | 2024-01-08 |
| 3091LSF | 12347003 | Volkswagen | Golf | 8065 | 02064836B | 2001-04-02 |
| 3877FFT | 74832134 | Audi | Q2 | 58737 | 01809637C | 2001-12-05 |
| 7012DLM | 84012398 | Peugeot | 207 | 49623 | 01809637C | 2001-11-28 |
+-----------+------------+------------+--------+-------+----------------+-------------+
```
:::
### Eliminació de registres de múltiples taules
També es poden eliminar dades de múltiples taules. En aquest cas, cal definir
quines taules es veuran afectades en la clàusula `DELETE`.
:::: example "DELETE amb múltiples taules"
Eliminem la persona amb DNI '01809637C' i els cotxes associats.
::: warning
S'han de deshabilitar les comprovacions de les claus foranes en aquest example.
:::
```sql
SET FOREIGN_KEY_CHECKS=0;
DELETE c, p
FROM persona p
INNER JOIN cotxe c
ON p.dni = c.dni_propietari
WHERE dni = "01809637C";
SET FOREIGN_KEY_CHECKS=1;
SELECT * FROM persona;
+-----------+--------+
| dni | nom |
+-----------+--------+
| 01780605Z | Marta |
| 02064836B | Daniel |
| 02827316H | Laura |
| 80594552E | Pere |
+-----------+--------+
SELECT * FROM cotxe;
+-----------+------------+------------+-------+------+----------------+-------------+
| matricula | n_bastidor | marca | model | km | dni_propietari | data_compra |
+-----------+------------+------------+-------+------+----------------+-------------+
| 1034KMC | 56481093 | BMW | X3 | 8480 | NULL | NULL |
| 3091LSF | 12347003 | Volkswagen | Golf | 8065 | 02064836B | 2001-04-02 |
+-----------+------------+------------+-------+------+----------------+-------------+
```
::::
## `ON UPDATE` i `ON DELETE`
La modificació i eliminació de dades d'una taula que estan referenciades en una altra taula
mitjançant una clau forana pot portar a problemes d'integritat. En el moment que es defineix una clau forana,
pots establir quin comportament tindrà la base de dades quan es modifique o s'eliminie un valor referenciat
(Vegeu [Material: DDL - Clau Forana](/itb/DAM-BD/UD4/materials/01_ddl.html)).
Fem un repàs dels possibles comportaments:
- `ON {DELETE | UPDATE}`: Indica el comportament de la taula quan s'esborra o es modifica
l'element al qual es fa referència en la clau forana.
- `RESTRICT`: No deixa eliminar o modificar l'element. Valor per defecte.
- `CASCADE`: Elimina també les entrades referenciades.
Si un element de la taula pare és eliminat o modificat, l'eliminació o modificació
es propagara a tots els elements de la taula filla que el referencien.
- `SET NULL`: El valor de la clau forana passa a ser `NULL`.
- `NO ACTION`: Sinònim de `RESTRICT`.
- `SET DEFAULT`: El valor de la clau forana passa a ser el valor per defecte especificat en la definició de la columna.
Una clau forana pot tindre comportaments diferents per a l'eliminació `DELETE`
i la modificació `UPDATE`.
::: docs
Documentació oficial sobre les claus foranes:
- MariaDB:
- https://mariadb.com/kb/en/foreign-keys/
- https://mariadb.com/kb/en/create-table/#foreign-key
- MySQL 8.0: https://dev.mysql.com/doc/refman/8.0/en/create-table-foreign-keys.html
:::
### `RESTRICT` o `NO ACTION`
::: example "RESTRICT o NO ACTION"
Per defecte, les claus foranes es defineixen com `ON DELETE RESTRICT` i `ON UPDATE RESTRICT`.
Aquest comportament és el predefinit quan no s'estableix res.
```sql
CREATE TABLE cotxe (
...
FOREIGN KEY (dni_propietari) REFERENCES persona (dni)
ON DELETE RESTRICT
ON UPDATE RESTRICT
);
```
En aquest cas, no es pot eliminar una persona referenciada en la taula `cotxe`.
Tampoc es pot modificar el valor referenciat (la clau primària).
```sql
SELECT * FROM cotxe;
+-----------+------------+------------+--------+-------+----------------+-------------+
| matricula | n_bastidor | marca | model | km | dni_propietari | data_compra |
+-----------+------------+------------+--------+-------+----------------+-------------+
| 1034KMC | 56481093 | BMW | X3 | 8480 | NULL | NULL |
| 3091LSF | 12347003 | Volkswagen | Golf | 8065 | 02064836B | 2001-04-02 |
| 3877FFT | 74832134 | Audi | Q2 | 57737 | 01809637C | 2001-12-05 |
| 4078CMP | 23948756 | Ford | Fiesta | 10542 | 02827316H | 2001-01-02 |
| 4231KJL | 16483920 | Ford | Focus | 39031 | 01727468F | 2000-12-08 |
| 7012DLM | 84012398 | Peugeot | 207 | 48623 | 01809637C | 2001-11-28 |
+-----------+------------+------------+--------+-------+----------------+-------------+
UPDATE persona
SET dni = "80594552E"
WHERE dni = "01809637C";
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails
(`cotxe`.`cotxe`, CONSTRAINT `cotxe_ibfk_1` FOREIGN KEY (`dni_propietari`) REFERENCES `persona` (`dni`))
DELETE FROM persona
WHERE dni = "01809637C";
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails
(`cotxe`.`cotxe`, CONSTRAINT `cotxe_ibfk_1` FOREIGN KEY (`dni_propietari`) REFERENCES `persona` (`dni`))
```
En les consultes anteriors hem hagut de deshabilitar les comprovacions de les claus foranes per poder eliminar
i modificar elements referenciats per una clau forana definida com `RESTRICT`.
:::
### `CASCADE`
::: example "CASCADE"
En aquest cas, definim la clau forana com `ON DELETE CASCADE` i `ON UPDATE CASCADA`.
Aquest comportament elimina o modifica "_en cascada_", és a dir,
que també elimina o modifica tots els elements referenciats.
```sql
CREATE TABLE cotxe (
...
FOREIGN KEY (dni_propietari) REFERENCES persona (dni)
ON DELETE CASCADE
ON UPDATE CASCADE
);
```
En aquest cas, si eliminem una persona, s'eliminaran els cotxes els qual
referencien a aquesta persona.
Si modifiquem el valor referenciat (clau primària), també es modificarà
en la clau forana.
```sql
SELECT * FROM persona p
RIGHT JOIN cotxe c
ON p.dni = c.dni_propietari;
+-----------+--------+-----------+------------+------------+--------+-------+----------------+-------------+
| dni | nom | matricula | n_bastidor | marca | model | km | dni_propietari | data_compra |
+-----------+--------+-----------+------------+------------+--------+-------+----------------+-------------+
| NULL | NULL | 1034KMC | 56481093 | BMW | X3 | 8480 | NULL | NULL |
| 01727468F | Pere | 4231KJL | 16483920 | Ford | Focus | 39031 | 01727468F | 2000-12-08 |
| 02064836B | Daniel | 3091LSF | 12347003 | Volkswagen | Golf | 8065 | 02064836B | 2001-04-02 |
| 02827316H | Laura | 4078CMP | 23948756 | Ford | Fiesta | 10542 | 02827316H | 2001-01-02 |
| 01809637C | Carla | 3877FFT | 74832134 | Audi | Q2 | 57737 | 01809637C | 2001-12-05 |
| 01809637C | Carla | 7012DLM | 84012398 | Peugeot | 207 | 48623 | 01809637C | 2001-11-28 |
+-----------+--------+-----------+------------+------------+--------+-------+----------------+-------------+
UPDATE persona
SET dni = "80594552E"
WHERE dni = "01809637C";
SELECT * FROM persona p
RIGHT JOIN cotxe c
ON p.dni = c.dni_propietari;
+-----------+--------+-----------+------------+------------+--------+-------+----------------+-------------+
| dni | nom | matricula | n_bastidor | marca | model | km | dni_propietari | data_compra |
+-----------+--------+-----------+------------+------------+--------+-------+----------------+-------------+
| NULL | NULL | 1034KMC | 56481093 | BMW | X3 | 8480 | NULL | NULL |
| 01727468F | Pere | 4231KJL | 16483920 | Ford | Focus | 39031 | 01727468F | 2000-12-08 |
| 02064836B | Daniel | 3091LSF | 12347003 | Volkswagen | Golf | 8065 | 02064836B | 2001-04-02 |
| 02827316H | Laura | 4078CMP | 23948756 | Ford | Fiesta | 10542 | 02827316H | 2001-01-02 |
| 80594552E | Carla | 3877FFT | 74832134 | Audi | Q2 | 57737 | 80594552E | 2001-12-05 |
| 80594552E | Carla | 7012DLM | 84012398 | Peugeot | 207 | 48623 | 80594552E | 2001-11-28 |
+-----------+--------+-----------+------------+------------+--------+-------+----------------+-------------+
DELETE FROM persona
WHERE dni = "80594552E";
SELECT * FROM persona p
RIGHT JOIN cotxe c
ON p.dni = c.dni_propietari;
+-----------+--------+-----------+------------+------------+--------+-------+----------------+-------------+
| dni | nom | matricula | n_bastidor | marca | model | km | dni_propietari | data_compra |
+-----------+--------+-----------+------------+------------+--------+-------+----------------+-------------+
| NULL | NULL | 1034KMC | 56481093 | BMW | X3 | 8480 | NULL | NULL |
| 02064836B | Daniel | 3091LSF | 12347003 | Volkswagen | Golf | 8065 | 02064836B | 2001-04-02 |
| 02827316H | Laura | 4078CMP | 23948756 | Ford | Fiesta | 10542 | 02827316H | 2001-01-02 |
| 01727468F | Pere | 4231KJL | 16483920 | Ford | Focus | 39031 | 01727468F | 2000-12-08 |
+-----------+--------+-----------+------------+------------+--------+-------+----------------+-------------+
```
:::
### `SET NULL`
::: example "SET NULL"
En aquest cas, definim la clau forana com `ON DELETE SET NULL` i `ON UPDATE SET NULL`.
Aquest comportament estableix a `NULL` el valor de la clau forana si s'elimina
el registre o es modifica el valor referenciat.
```sql
CREATE TABLE cotxe (
...
FOREIGN KEY (dni_propietari) REFERENCES persona (dni)
ON DELETE SET NULL
ON UPDATE SET NULL
);
```
En aquest cas, si eliminem una persona, el valor `dni_propietari`
passarà a ser `NULL`.
Si modifiquem el valor referenciat (clau primària), passarà el mateix.
```sql
SELECT * FROM persona p
RIGHT JOIN cotxe c
ON p.dni = c.dni_propietari;
+-----------+--------+-----------+------------+------------+--------+-------+----------------+-------------+
| dni | nom | matricula | n_bastidor | marca | model | km | dni_propietari | data_compra |
+-----------+--------+-----------+------------+------------+--------+-------+----------------+-------------+
| NULL | NULL | 1034KMC | 56481093 | BMW | X3 | 8480 | NULL | NULL |
| 01727468F | Pere | 4231KJL | 16483920 | Ford | Focus | 39031 | 01727468F | 2000-12-08 |
| 02064836B | Daniel | 3091LSF | 12347003 | Volkswagen | Golf | 8065 | 02064836B | 2001-04-02 |
| 02827316H | Laura | 4078CMP | 23948756 | Ford | Fiesta | 10542 | 02827316H | 2001-01-02 |
| 01809637C | Carla | 3877FFT | 74832134 | Audi | Q2 | 57737 | 01809637C | 2001-12-05 |
| 01809637C | Carla | 7012DLM | 84012398 | Peugeot | 207 | 48623 | 01809637C | 2001-11-28 |
+-----------+--------+-----------+------------+------------+--------+-------+----------------+-------------+
UPDATE persona
SET dni = "80594552E"
WHERE dni = "01809637C";
SELECT * FROM persona p
RIGHT JOIN cotxe c
ON p.dni = c.dni_propietari;
+-----------+--------+-----------+------------+------------+--------+-------+----------------+-------------+
| dni | nom | matricula | n_bastidor | marca | model | km | dni_propietari | data_compra |
+-----------+--------+-----------+------------+------------+--------+-------+----------------+-------------+
| NULL | NULL | 1034KMC | 56481093 | BMW | X3 | 8480 | NULL | NULL |
| 02064836B | Daniel | 3091LSF | 12347003 | Volkswagen | Golf | 8065 | 02064836B | 2001-04-02 |
| NULL | NULL | 3877FFT | 74832134 | Audi | Q2 | 57737 | NULL | 2001-12-05 |
| 02827316H | Laura | 4078CMP | 23948756 | Ford | Fiesta | 10542 | 02827316H | 2001-01-02 |
| 01727468F | Pere | 4231KJL | 16483920 | Ford | Focus | 39031 | 01727468F | 2000-12-08 |
| NULL | NULL | 7012DLM | 84012398 | Peugeot | 207 | 48623 | NULL | 2001-11-28 |
+-----------+--------+-----------+------------+------------+--------+-------+----------------+-------------+
DELETE FROM persona
WHERE dni = "01727468F";
SELECT * FROM persona p
RIGHT JOIN cotxe c
ON p.dni = c.dni_propietari;
+-----------+--------+-----------+------------+------------+--------+-------+----------------+-------------+
| dni | nom | matricula | n_bastidor | marca | model | km | dni_propietari | data_compra |
+-----------+--------+-----------+------------+------------+--------+-------+----------------+-------------+
| NULL | NULL | 1034KMC | 56481093 | BMW | X3 | 8480 | NULL | NULL |
| 02064836B | Daniel | 3091LSF | 12347003 | Volkswagen | Golf | 8065 | 02064836B | 2001-04-02 |
| NULL | NULL | 3877FFT | 74832134 | Audi | Q2 | 57737 | NULL | 2001-12-05 |
| 02827316H | Laura | 4078CMP | 23948756 | Ford | Fiesta | 10542 | 02827316H | 2001-01-02 |
| NULL | NULL | 4231KJL | 16483920 | Ford | Focus | 39031 | NULL | 2000-12-08 |
| NULL | NULL | 7012DLM | 84012398 | Peugeot | 207 | 48623 | NULL | 2001-11-28 |
+-----------+--------+-----------+------------+------------+--------+-------+----------------+-------------+
```
:::
### `SET DEFAULT`
::::: example "SET DEFAULT"
Si definim la clau forana com `ON DELETE SET DEFAULT` i `ON UPDATE SET DEFAULT`.
s'estableix al valor definit per defecte el valor de la clau forana si s'elimina
el registre o es modifica el valor referenciat.
:::: error
Extracte de la documentació:
> __`SET DEFAULT`__: This action is recognized by the MySQL parser,
> but both `InnoDB` and `NDB` reject table definitions containing
> `ON DELETE SET DEFAULT` or `ON UPDATE SET DEFAULT` clauses.
En aquest cas, ens deixa crear la definició de la taula ja
que la clàusula sí que pertany al llenguatge SQL i està suportat
pel parser de MySQL, però el motor `InnoBD` no implementa
el comportament definit, per tant, es comporta com `RESTRICT`.
::::
```sql
CREATE TABLE cotxe (
...
dni_propietari CHAR(9) DEFAULT "01727468F",
FOREIGN KEY (dni_propietari) REFERENCES persona (dni)
ON DELETE SET DEFAULT
ON UPDATE SET DEFAULT
);
```
En aquest cas, si eliminem una persona, el valor `dni_propietari`
passarà a ser "01727468F".
Si modifiquem el valor referenciat (clau primària), passarà el mateix.
:::: error
No ho fa perquè aquesta opció no està implementada en el motor `InnoBD`.
::::
```sql
SELECT * FROM persona p
RIGHT JOIN cotxe c
ON p.dni = c.dni_propietari;
+-----------+--------+-----------+------------+------------+--------+-------+----------------+-------------+
| dni | nom | matricula | n_bastidor | marca | model | km | dni_propietari | data_compra |
+-----------+--------+-----------+------------+------------+--------+-------+----------------+-------------+
| NULL | NULL | 1034KMC | 56481093 | BMW | X3 | 8480 | NULL | NULL |
| 01727468F | Pere | 4231KJL | 16483920 | Ford | Focus | 39031 | 01727468F | 2000-12-08 |
| 02064836B | Daniel | 3091LSF | 12347003 | Volkswagen | Golf | 8065 | 02064836B | 2001-04-02 |
| 02827316H | Laura | 4078CMP | 23948756 | Ford | Fiesta | 10542 | 02827316H | 2001-01-02 |
| 01809637C | Carla | 3877FFT | 74832134 | Audi | Q2 | 57737 | 01809637C | 2001-12-05 |
| 01809637C | Carla | 7012DLM | 84012398 | Peugeot | 207 | 48623 | 01809637C | 2001-11-28 |
+-----------+--------+-----------+------------+------------+--------+-------+----------------+-------------+
UPDATE persona
SET dni = "80594552E"
WHERE dni = "01809637C";
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails
(`cotxe`.`cotxe`, CONSTRAINT `cotxe_ibfk_1` FOREIGN KEY (`dni_propietari`) REFERENCES `persona` (`dni`))
DELETE FROM persona
WHERE dni = "01727468F";
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails
(`cotxe`.`cotxe`, CONSTRAINT `cotxe_ibfk_1` FOREIGN KEY (`dni_propietari`) REFERENCES `persona` (`dni`))
```
:::::
## Bibliografia
- https://soporte.miarroba.com/17452/11588522-solucionado-accion-on-set-null-default-en-mysql/
- https://dev.mysql.com/doc/refman/8.0/en/create-table-foreign-keys.html