Oracle - FOREIGN KEY

Oracle | FOREIGN KEY

Notes
Créer une clé étrangère
1. Créer une clé étrangère avec la syntaxe : CREATE TABLE
2. Créer une clé étrangère avec la syntaxe : ALTER TABLE
Créer une clé étrangère : ON DELETE CASCADE
Créer une clé étrangère : ON DELETE SET NULL
Désactiver une clé étrangère : DISABLE CONSTRAINT
Activer une clé étrangère : ENABLE CONSTRAINT
Supprimer une clé étrangère : DROP CONSTRAINT

Notes
- Une clé étrangère est une contrainte définie sur une ou plusieurs colonnes de la table.
- Les colonnes de la clé étrangère référencent les colonnes de la clé primaire (ou la clé unique) de la table référencée.
This table is used in the examples bellow:
CREATE TABLE table_unique_1 ( id NUMBER(1,0) , id1 NUMBER(1,0) , id2 NUMBER(1,0) , CONSTRAINT cp_table_unique_1_id PRIMARY KEY (id) -- PRIMARY KEY , CONSTRAINT cu_table_unique_1_id UNIQUE (id1, id2) -- UNIQUE );
Créer une clé étrangère
1. Créer une clé étrangère avec la syntaxe : CREATE TABLE
  CREATE TABLE table_foreign_key_1 ( id NUMBER(1,0) , CONSTRAINT cr_table_foreign_key_1_id FOREIGN KEY (id) REFERENCES table_unique_1 (id) -- le nom de la contrainte de clé étrangère est explicite "CR_table_foreign_key_1_ID" );
  Important : La colonne (id) référencée par la clé étrangère doit avoir une contrainte de clé primaire (PRIMARY KEY) ou une contrainte d'incitée (UNIQUE).
  
  Remarque : Si on omet le nom de la contrainte, Oracle va générer un nom automatique :
  CREATE TABLE table_foreign_key_2 ( id NUMBER(1,0) , FOREIGN KEY (id) REFERENCES table_unique_1 (id) -- le nom de la contrainte de clé étrangère sera générer dynamiquement par Oracle );
  Remarque : Si la clé étrangère est une clé composée de plusieurs colonnes, alors il faut spécifier ces colonnes séparées par des points virgules (column1, column2, …).
  
  CREATE TABLE table_foreign_key_3 ( id NUMBER(1,0) , fk_id1 NUMBER(1,0) , fk_id2 NUMBER(1,0) , CONSTRAINT cr_table_foreign_key_3_id FOREIGN KEY (fk_id1, fk_id2) REFERENCES table_unique_1 (id1, id2) );
2. Créer une clé étrangère avec la syntaxe : ALTER TABLE
  CREATE TABLE table_foreign_key_4 ( id NUMBER(1,0) );
  ALTER TABLE table_foreign_key_4 ADD CONSTRAINT cr_table_foreign_key_4_id FOREIGN KEY (id) REFERENCES table_unique_1 (id); -- le nom de la contrainte de clé étrangère est explicite "CR_TABLE_FOREIGN_KEY_4_ID"
  Ou :
  ALTER TABLE table_foreign_key_4 ADD FOREIGN KEY (id) REFERENCES table_unique_1 (id); -- le nom de la contrainte de clé étrangère sera générer dynamiquement par Oracle
Créer une clé étrangère : ON DELETE CASCADE
CREATE TABLE table_foreign_key_5 ( id NUMBER(1,0) , CONSTRAINT cr_table_foreign_key_5_id FOREIGN KEY (id) REFERENCES table_unique_1 (id) ON DELETE CASCADE );
L'option "ON DELETE CASCADE" indique que si une ligne est supprimée de la table "table_unique_1" alors les lignes qui la référencent dans la table "table_foreign_key_5" seront aussi supprimées.

Vérifier la création de la contrainte :
SELECT constraint_name, constraint_type, generated, delete_rule FROM user_constraints WHERE table_name = 'table_foreign_key_5';
Résultat de la requête : +---------------------------+-----------------+----------------+-------------+ | CONSTRAINT_NAME | CONSTRAINT_TYPE | GENERATED | DELETE_RULE | +---------------------------+-----------------+----------------+-------------+ | CR_TABLE_FOREIGN_KEY_5_ID | R | GENERATED NAME | CASCADE | +---------------------------+-----------------+----------------+-------------+
Teste :
INSERT INTO table_unique_1 (id, id1, id2) VALUES (0,0,0); INSERT INTO table_unique_1 (id, id1, id2) VALUES (1,0,1); INSERT INTO table_unique_1 (id, id1, id2) VALUES (2,1,0);
INSERT INTO table_foreign_key_5 (id) VALUES (0); INSERT INTO table_foreign_key_5 (id) VALUES (1); INSERT INTO table_foreign_key_5 (id) VALUES (2);
DELETE FROM table_unique_1 WHERE id < 2;
Vérifier les données de la table "table_foreign_key_5" après la suppression des données de la table "table_unique_1" :
SELECT id FROM table_foreign_key_5;
+----+ | ID | +----+ | 2 | +----+
Créer une clé étrangère : ON DELETE SET NULL
CREATE TABLE table_foreign_key_6 ( id NUMBER(1,0) , CONSTRAINT cr_table_foreign_key_6_id FOREIGN KEY (id) REFERENCES table_unique_1 (id) ON DELETE SET NULL );
L'option "ON DELETE SET NULL" indique que si une ligne est supprimée de la table "table_unique_1" alors les colonnes (clé étrangère) de toutes les lignes qui la référencent dans la table "table_foreign_key_6" seront initialisées à NULL.

Vérifier la création de la contrainte :
SELECT constraint_name, constraint_type, generated, delete_rule FROM user_constraints WHERE table_name = 'table_foreign_key_6';
Résultat de la requête : +---------------------------+-----------------+----------------+-------------+ | CONSTRAINT_NAME | CONSTRAINT_TYPE | GENERATED | DELETE_RULE | +---------------------------+-----------------+----------------+-------------+ | CR_TABLE_FOREIGN_KEY_6_ID | R | GENERATED NAME | SET NULL | +---------------------------+-----------------+----------------+-------------+
Teste :
INSERT INTO table_unique_1 (id, id1, id2) VALUES (0,0,0); INSERT INTO table_unique_1 (id, id1, id2) VALUES (1,0,1); INSERT INTO table_unique_1 (id, id1, id2) VALUES (2,1,0);
INSERT INTO table_foreign_key_6 (id) VALUES (0); INSERT INTO table_foreign_key_6 (id) VALUES (1); INSERT INTO table_foreign_key_6 (id) VALUES (2);
DELETE FROM table_unique_1 WHERE id < 2;
Vérifier les données de la table "table_foreign_key_6" après la suppression des données de la table "table_unique_1" :
SELECT id FROM table_foreign_key_6;
+--------+ | ID | +--------+ | (null) | | (null) | | 2 | +--------+
Désactiver une clé étrangère : DISABLE CONSTRAINT
ALTER TABLE table_foreign_key_1 DISABLE CONSTRAINT cr_table_foreign_key_1_id;
Activer une clé étrangère : ENABLE CONSTRAINT
ALTER TABLE table_foreign_key_1 ENABLE CONSTRAINT cr_table_foreign_key_1_id;
Supprimer une clé étrangère : DROP CONSTRAINT
ALTER TABLE table_foreign_key_1 DROP CONSTRAINT cr_table_foreign_key_1_id;
Attention : Supprimer la clé étrangère est nécessaire si on veut supprimer la table référencée, au cas contraire Oracle affichera une erreur :
DROP TABLE table_unique_1;
Résultat de la requête : Error starting at line 1 in command: DROP TABLE TABLE_UNIQUE_1 Error report: SQL Error: ORA-02449: unique/primary keys in table referenced by foreign keys. *Cause: An attempt was made to drop a table with unique or primary keys referenced by foreign keys in another table. *Action: Before performing the above operations the table, drop the foreign key constraints in other tables. You can see what constraints are referencing a table by issuing the following command: SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = "tabnam";