L3 Info : SGBD
 
◃  Ch. 7 SQL : DML  ▹
 

Mises à jour et suppressions automatiques

  • Comment s'assurer que l'édition de données ne laisse pas de références orphelines ?
  • Interdire les éditions : comportement par défaut éventuellement renforcé par les clauses [ON DELETE RESTRICT][ON UPDATE RESTRICT]
  • Forcer les éditions impliquées : en ajoutant les clauses [ON DELETE CASCADE][ON UPDATE CASCADE] aux déclarations de clés étrangères
  • Remplacer les valeurs de références : [ON DELETE SET NULL] ou [ON UDPATE SET DEFAULT]
  • Exemple : 
    CREATE TABLE Etudiant (
   etud_num INTEGER PRIMARY KEY ,
   etud_nom VARCHAR(30) NOT NULL,
   etud_prenom VARCHAR(20) NOT NULL,
   etud_adresse VARCHAR(80) NOT NULL,
   etud_naissance DATE,
   dip_id INTEGER,
   FOREIGN KEY (dip_id) REFERENCES diplome(dip_id) ON DELETE CASCADE ON UPDATE CASCADE
   );
  • Contenu des tables diplome et etudiant : 
    pgl2mi=# SELECT * FROM diplome;
 dip_id | dip_nom | dip_niveau | dip_discipline | prof_id
--------+---------+------------+----------------+---------
      1 | Master  | M2         | Informatique   |       1
      2 | Licence | L3         | Informatique   |       3
      3 | Licence | L2         | Informatique   |       3
(3 rows)

pgl2mi=# SELECT * FROM etudiant;
 etud_num | etud_nom | etud_prenom |       etud_adresse        | etud_naissance | dip_id
----------+----------+-------------+---------------------------+----------------+--------
 20000001 | Martin   | Jacques     | Résidence de la pommeraie | 1995-01-01     |      1
 20000002 | Martin   | Pierre      | Résidence de la pommeraie | 1995-10-21     |      1
 20000003 | Dalton   | Joe         | Pénitencier fédéral       | 1865-04-01     |
 20000004 | Dalton   | Joe         | Pénitencier fédéral       | 1866-07-04     |
 20000005 | Dalton   | Joe         | Pénitencier fédéral       | 1867-11-28     |
 20000006 | Dalton   | Joe         | Pénitencier fédéral       | 1868-12-25     |
 20000044 | Arthur   | Toto        | rue de la Buissonière     | 1970-01-01     |      3
(7 rows)
  • Modification de la valeur d'une clé de la table diplome (même si cette action est peu courante) : 
    pgl2mi=# UPDATE diplome SET dip_id=4 WHERE dip_id=3;
UPDATE 1
pgl2mi=# SELECT * FROM diplome;
 dip_id | dip_nom | dip_niveau | dip_discipline | prof_id
--------+---------+------------+----------------+---------
      1 | Master  | M2         | Informatique   |       1
      2 | Licence | L3         | Informatique   |       3
      4 | Licence | L2         | Informatique   |       3
(3 rows)

pgl2mi=# SELECT * FROM etudiant;
 etud_num | etud_nom | etud_prenom |       etud_adresse        | etud_naissance | dip_id
----------+----------+-------------+---------------------------+----------------+--------
 20000001 | Martin   | Jacques     | Résidence de la pommeraie | 1995-01-01     |      1
 20000002 | Martin   | Pierre      | Résidence de la pommeraie | 1995-10-21     |      1
 20000003 | Dalton   | Joe         | Pénitencier fédéral       | 1865-04-01     |
 20000004 | Dalton   | Joe         | Pénitencier fédéral       | 1866-07-04     |
 20000005 | Dalton   | Joe         | Pénitencier fédéral       | 1867-11-28     |
 20000006 | Dalton   | Joe         | Pénitencier fédéral       | 1868-12-25     |
 20000044 | Arthur   | Toto        | rue de la Buissonière     | 1970-01-01     |      4
(7 rows)
  • Suppression du diplome numéro 4 : 
    pgl2mi=# DELETE FROM diplome WHERE dip_id=4;
DELETE 1
pgl2mi=# SELECT * FROM diplome;
 dip_id | dip_nom | dip_niveau | dip_discipline | prof_id
--------+---------+------------+----------------+---------
      1 | Master  | M2         | Informatique   |       1
      2 | Licence | L3         | Informatique   |       3
(2 rows)

pgl2mi=# SELECT * FROM etudiant;
 etud_num | etud_nom | etud_prenom |       etud_adresse        | etud_naissance | dip_id
----------+----------+-------------+---------------------------+----------------+--------
 20000001 | Martin   | Jacques     | Résidence de la pommeraie | 1995-01-01     |      1
 20000002 | Martin   | Pierre      | Résidence de la pommeraie | 1995-10-21     |      1
 20000003 | Dalton   | Joe         | Pénitencier fédéral       | 1865-04-01     |
 20000004 | Dalton   | Joe         | Pénitencier fédéral       | 1866-07-04     |
 20000005 | Dalton   | Joe         | Pénitencier fédéral       | 1867-11-28     |
 20000006 | Dalton   | Joe         | Pénitencier fédéral       | 1868-12-25     |
(6 rows)
    L'étudiant Toto a été supprimé ! Dans ce cas l'option [ON DELETE SET NULL] est préférable.
  • Nouvelle définition de la table Etudiant, et inscription des Dalton dans le diplome numéro 3. 
    CREATE TABLE Etudiant (
   etud_num INTEGER PRIMARY KEY ,
   etud_nom VARCHAR(30) NOT NULL,
   etud_prenom VARCHAR(20) NOT NULL,
   etud_adresse VARCHAR(80) NOT NULL,
   etud_naissance DATE,
   dip_id INTEGER,
   FOREIGN KEY (dip_id) REFERENCES diplome(dip_id) ON DELETE SET NULL ON UPDATE CASCADE
   );
   INSERT INTO diplome VALUES(3,'Licence','L2','Informatique',2);
   UPDATE Etudiant SET dip_id=3 WHERE etud_nom='Dalton';
  • Supression du diplome numéro 3 et effet sur la table Etudiant : 
    pgl2mi=# SELECT * FROM diplome;
 dip_id | dip_nom | dip_niveau | dip_discipline | prof_id
--------+---------+------------+----------------+---------
      1 | Master  | M2         | Informatique   |       1
      2 | Licence | L3         | Informatique   |       3
      3 | Licence | L2         | Informatique   |       2
(3 rows)

pgl2mi=# SELECT * FROM etudiant;
 etud_num | etud_nom | etud_prenom |       etud_adresse        | etud_naissance | dip_id
----------+----------+-------------+---------------------------+----------------+--------
 20000001 | Martin   | Jacques     | Résidence de la pommeraie | 1995-01-01     |      1
 20000002 | Martin   | Pierre      | Résidence de la pommeraie | 1995-10-21     |      1
 20000003 | Dalton   | Joe         | Pénitencier fédéral       | 1865-04-01     |      3
 20000004 | Dalton   | Joe         | Pénitencier fédéral       | 1866-07-04     |      3
 20000005 | Dalton   | Joe         | Pénitencier fédéral       | 1867-11-28     |      3
 20000006 | Dalton   | Joe         | Pénitencier fédéral       | 1868-12-25     |      3
(6 rows)

pgl2mi=# DELETE FROM diplome WHERE dip_id =3;
DELETE 1

pgl2mi=# SELECT * FROM diplome;
 dip_id | dip_nom | dip_niveau | dip_discipline | prof_id
--------+---------+------------+----------------+---------
      1 | Master  | M2         | Informatique   |       1
      2 | Licence | L3         | Informatique   |       3
(2 rows)

pgl2mi=# SELECT * FROM etudiant;
 etud_num | etud_nom | etud_prenom |       etud_adresse        | etud_naissance | dip_id
----------+----------+-------------+---------------------------+----------------+--------
 20000001 | Martin   | Jacques     | Résidence de la pommeraie | 1995-01-01     |      1
 20000002 | Martin   | Pierre      | Résidence de la pommeraie | 1995-10-21     |      1
 20000003 | Dalton   | Joe         | Pénitencier fédéral       | 1865-04-01     |
 20000004 | Dalton   | Joe         | Pénitencier fédéral       | 1866-07-04     |
 20000005 | Dalton   | Joe         | Pénitencier fédéral       | 1867-11-28     |
 20000006 | Dalton   | Joe         | Pénitencier fédéral       | 1868-12-25     |
(6 rows)
    Les Dalton ne sont pas effacés, seul leur dip_id a été remplacé par NULL