L2 Info : Bases de données
 
◃  Ch. 6 Retour sur DDL  ▹
 

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