Salta el contingut
 

Modificació i eliminació de registres

Joan Puigcerver Ibáñez

j.puigcerveribanez@edu.gva.es

Llicència: CC BY-NC-SA 4.0

(Reconeixement - NoComercial - CompartirIgual) 🅭

License

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: 

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.

Documentació

Documentació oficial sobre modificació de registres:

Dades d'exemple

Perill

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: 

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.

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'. 

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.

UPDATE amb valors concrets

En aquest cas, modifiquem el cotxe '1034KMC' i establim els quilòmetres al valor per defecte. 

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.

UPDATE a partir d'un altre valor

En aquest cas, modifiquem els quilòmetres del cotxe '1034KMC' per sumar-li 10km més. 

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.

UPDATE múltiples registres

En aquest cas, modifiquem els quilòmetres del cotxes 'Ford' per augmentar en 100 els quilòmetres. 

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.

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. 

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.

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.

Perill

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: 

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


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: 

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.

Documentació

Documentació oficial sobre eliminació de registres:

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.

DELETE amb una taula

Eliminem els cotxes de la 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  |
| 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.

DELETE amb múltiples taules

Eliminem la persona amb DNI '01809637C' i els cotxes associats.

Advertència

S'han de deshabilitar les comprovacions de les claus foranes en aquest example.

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).

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.

Documentació

Documentació oficial sobre les claus foranes:

RESTRICT o NO ACTION

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.

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).

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

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.

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.

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

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.

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.

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

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.

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.

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

📌 Aquest document pot quedar desactualitzat després d’imprimir-lo. Pots consultar la versió més recent a la pàgina web.
🌿 Abans d’imprimir aquest document, considera si és realment necessari. Redueix el consum de paper i ajuda a protegir el nostre entorn.
 
CC BY-NC-SA 4.0 (Reconeixement - NoComercial - CompartirIgual) 🅭 Joan Puigcerver Ibáñez