Contraintes d'intégrité et clés étrangères

PostgreSQL

• Clé étrangère entre Diplome et Professeur (responsable diplôme)

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

• Tentative d'insertion d'un diplome avec un identifiant de professeur non valide :

  pgl2mi=# INSERT INTO diplome VALUES (3, 'Licence', 'L2', 'Informatique', 24);
  ERROR:  insert or update on table "diplome" violates foreign key constraint "diplome_prof_id_fkey"
  DETAIL:  Key (prof_id)=(24) is not present in table "professeur".

• Tentative de suppression d'un professeur responsable de diplôme

  pgl2mi=# DELETE FROM professeur WHERE prof_id=1;
  ERROR:  update or delete on table "professeur" violates foreign key constraint 
  "diplome_prof_id_fkey" on table "diplome"
  DETAIL:  Key (prof_id)=(1) is still referenced from table "diplome".
  pgl2mi=# 

• Tentative de modification de l'identifiant d'un professeur responsable de diplôme :

  pgl2mi=# UPDATE professeur SET prof_id=8 WHERE prof_id=1;
  ERROR:  update or delete on table "professeur" violates foreign key constraint 
  "diplome_prof_id_fkey" on table "diplome"
  DETAIL:  Key (prof_id)=(1) is still referenced from table "diplome".

MySQL

mysql> DELETE FROM Professeur WHERE prof_id=1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint
                    fails (`dominique`.`Diplome`, CONSTRAINT `Diplome_ibfk_1` 
                    FOREIGN KEY (`prof_id`) REFERENCES `Professeur` (`prof_id`))

• MAIS Dépend de la déclaration de la contrainte, si la déclaration de clé étrangère n'est pas explicite comme ici :

  mysql> 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 REFERENCES diplome(dip_id) 
     );

  mysql> 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     |   NULL |
  | 20000004 | Dalton   | Jack        | Pénitencier fédéral        | 1866-07-04     |   NULL |
  | 20000005 | Dalton   | William     | Pénitencier fédéral        | 1867-11-28     |   NULL |
  | 20000006 | Dalton   | Averell     | Pénitencier fédéral        | 1868-12-25     |   NULL |
  +----------+----------+-------------+----------------------------+----------------+--------+
  6 rows in set (0,00 sec)

  La suppression du diplome numéro 1 laisse deux enregistrements orphelins dans la table Etudiant :

  mysql> DELETE FROM Diplome WHERE dip_id=1;
  Query OK, 1 row affected (0,00 sec)

  mysql> 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     |   NULL |
  | 20000004 | Dalton   | Jack        | Pénitencier fédéral        | 1866-07-04     |   NULL |
  | 20000005 | Dalton   | William     | Pénitencier fédéral        | 1867-11-28     |   NULL |
  | 20000006 | Dalton   | Averell     | Pénitencier fédéral        | 1868-12-25     |   NULL |
  +----------+----------+-------------+----------------------------+----------------+--------+
  6 rows in set (0,00 sec)