LDD - Cascade et constraintes

Drop table cascade constraints

Données de test

Instructions à exécuter pour créer des données de test :

DROP TABLE EMP;
DROP TABLE DEPT;
CREATE TABLE DEPT (
    DEPTNO           NUMBER(2),
    DNAME            VARCHAR2(15),
    LOC              VARCHAR2(15),
    CONSTRAINT DEPT_DEPTNO_PK PRIMARY KEY (DEPTNO)
);

CREATE TABLE EMP (
    EMPNO            NUMBER(4),
    ENAME            VARCHAR2(10) NOT NULL,
    JOB              VARCHAR2(10),
    MGR              NUMBER(4) CONSTRAINT EMP_MGR_FK REFERENCES EMP (EMPNO),
    HIREDATE         DATE DEFAULT SYSDATE,
    SAL              NUMBER(7,2),
    COMM             NUMBER(7,2),
    DEPTNO           NUMBER(2) 	NOT NULL,
    CONSTRAINT EMP_EMPNO_PK PRIMARY KEY (EMPNO),
    CONSTRAINT EMP_DEPTNO_FK FOREIGN KEY (DEPTNO) REFERENCES DEPT (DEPTNO)
);

INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
INSERT INTO DEPT VALUES (30,'SALES','CHICAGO');
INSERT INTO DEPT VALUES (40,'OPERATIONS','BOSTON');

INSERT INTO EMP VALUES (7839,'KING','PRESIDENT',NULL,'17/11/1981',5000,NULL,10);
INSERT INTO EMP VALUES (7698,'BLAKE','MANAGER',7839,'01/05/1981',2850,NULL,30);
INSERT INTO EMP VALUES (7782,'CLARK','MANAGER',7839,'09/06/1981',2450,NULL,10);
INSERT INTO EMP VALUES (7566,'JONES','MANAGER',7839,'02/04/1981',2975,NULL,20);
INSERT INTO EMP VALUES (7654,'MARTIN','SALESMAN',7698,'28/09/1981',1250,1400,30);
INSERT INTO EMP VALUES (7499,'ALLEN','SALESMAN',7698,'20/02/1981',1600,300,30);
INSERT INTO EMP VALUES (7844,'TURNER','SALESMAN',7698,'08/09/1981',1500,0,30);
INSERT INTO EMP VALUES (7900,'JAMES','CLERK',7698,'03/12/1981',950,NULL,30);
INSERT INTO EMP VALUES (7521,'WARD','SALESMAN',7698,'22/02/1981',1250,500,30);
INSERT INTO EMP VALUES (7902,'FORD','ANALYST',7566,'03/12/1981',3000,NULL,20);
INSERT INTO EMP VALUES (7369,'SMITH','CLERK',7902,'17/12/1980',800,NULL,20);
INSERT INTO EMP VALUES (7788,'SCOTT','ANALYST',7566,'09/12/1982',3000,NULL,20);
INSERT INTO EMP VALUES (7876,'ADAMS','CLERK',7788,'12/01/1983',1100,NULL,20);
INSERT INTO EMP VALUES (7934,'MILLER','CLERK',7782,'23/01/1982',1300,NULL,10);

La table emp contient une référence vers la table dept par le biais de sa clé étrangère EMP_DEPTNO_FK.

Rappel : pour visualiser les contraintes :

SELECT constraint_name, constraint_type, search_condition
FROM user_constraints
WHERE table_name in('EMP', 'DEPT');

Règle

Drop table cascade constraints supprime les contraintes affectées par cette suppression, sans supprimer les données des tables liées.

Vérification

drop table dept cascade constraints;

select * from emp;

SELECT constraint_name, constraint_type, search_condition
FROM user_constraints
WHERE table_name in('EMP', 'DEPT');

On constate :

Les contraintes liées à la table dept ont disparu (ce qui est normal, puisque la table est supprimée).
Les données de la table emp n'ont pas été supprimées ; en perticulier, les valeurs de la colonne deptno n'ont pas été affectées.
La contrainte DEPT_DEPTNO_PK a été supprimée.

On delete cascade

Données de test

DROP TABLE EMP;
DROP TABLE DEPT;
CREATE TABLE DEPT (
    DEPTNO           NUMBER(2),
    DNAME            VARCHAR2(15),
    LOC              VARCHAR2(15),
    CONSTRAINT DEPT_DEPTNO_PK PRIMARY KEY (DEPTNO)
);

CREATE TABLE EMP (
    EMPNO            NUMBER(4),
    ENAME            VARCHAR2(10) NOT NULL,
    JOB              VARCHAR2(10),
    MGR              NUMBER(4) CONSTRAINT EMP_MGR_FK REFERENCES EMP (EMPNO),
    HIREDATE         DATE DEFAULT SYSDATE,
    SAL              NUMBER(7,2),
    COMM             NUMBER(7,2),
    DEPTNO           NUMBER(2) 	NOT NULL,
    CONSTRAINT EMP_EMPNO_PK PRIMARY KEY (EMPNO),
    CONSTRAINT EMP_DEPTNO_FK FOREIGN KEY (DEPTNO) REFERENCES DEPT (DEPTNO) ON DELETE CASCADE
);

INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
INSERT INTO DEPT VALUES (30,'SALES','CHICAGO');
INSERT INTO DEPT VALUES (40,'OPERATIONS','BOSTON');

INSERT INTO EMP VALUES (7839,'KING','PRESIDENT',NULL,'17/11/1981',5000,NULL,10);
INSERT INTO EMP VALUES (7698,'BLAKE','MANAGER',7839,'01/05/1981',2850,NULL,30);
INSERT INTO EMP VALUES (7782,'CLARK','MANAGER',7839,'09/06/1981',2450,NULL,10);
INSERT INTO EMP VALUES (7566,'JONES','MANAGER',7839,'02/04/1981',2975,NULL,20);
INSERT INTO EMP VALUES (7654,'MARTIN','SALESMAN',7698,'28/09/1981',1250,1400,30);
INSERT INTO EMP VALUES (7499,'ALLEN','SALESMAN',7698,'20/02/1981',1600,300,30);
INSERT INTO EMP VALUES (7844,'TURNER','SALESMAN',7698,'08/09/1981',1500,0,30);
INSERT INTO EMP VALUES (7900,'JAMES','CLERK',7698,'03/12/1981',950,NULL,30);
INSERT INTO EMP VALUES (7521,'WARD','SALESMAN',7698,'22/02/1981',1250,500,30);
INSERT INTO EMP VALUES (7902,'FORD','ANALYST',7566,'03/12/1981',3000,NULL,20);
INSERT INTO EMP VALUES (7369,'SMITH','CLERK',7902,'17/12/1980',800,NULL,20);
INSERT INTO EMP VALUES (7788,'SCOTT','ANALYST',7566,'09/12/1982',3000,NULL,20);
INSERT INTO EMP VALUES (7876,'ADAMS','CLERK',7788,'12/01/1983',1100,NULL,20);
INSERT INTO EMP VALUES (7934,'MILLER','CLERK',7782,'23/01/1982',1300,NULL,10);

Les données sont les mêmes que dans le paragraphe précédent, mais la définition de la EMP_DEPTNO_FK contient on delete cascade.

Règle

Lorsque on delete cascade est utilisée dans la définition d'une clé étrangère, le suppression d'une ligne entraîne la supression des lignes liées par la contrainte de clé étrangère.

Vérification

delete from dept where deptno=30;

select * from emp;

On voit que toutes les lignes de emp ayant un deptno=30 ont été supprimées.

Alter table Disable constraint cascade

Données de test

Identiques aux données du test sur drop table.

DROP TABLE EMP;
DROP TABLE DEPT;
CREATE TABLE DEPT (
    DEPTNO           NUMBER(2),
    DNAME            VARCHAR2(15),
    LOC              VARCHAR2(15),
    CONSTRAINT DEPT_DEPTNO_PK PRIMARY KEY (DEPTNO)
);

CREATE TABLE EMP (
    EMPNO            NUMBER(4),
    ENAME            VARCHAR2(10) NOT NULL,
    JOB              VARCHAR2(10),
    MGR              NUMBER(4) CONSTRAINT EMP_MGR_FK REFERENCES EMP (EMPNO),
    HIREDATE         DATE DEFAULT SYSDATE,
    SAL              NUMBER(7,2),
    COMM             NUMBER(7,2),
    DEPTNO           NUMBER(2) 	NOT NULL,
    CONSTRAINT EMP_EMPNO_PK PRIMARY KEY (EMPNO),
    CONSTRAINT EMP_DEPTNO_FK FOREIGN KEY (DEPTNO) REFERENCES DEPT (DEPTNO)
);

INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
INSERT INTO DEPT VALUES (30,'SALES','CHICAGO');
INSERT INTO DEPT VALUES (40,'OPERATIONS','BOSTON');

INSERT INTO EMP VALUES (7839,'KING','PRESIDENT',NULL,'17/11/1981',5000,NULL,10);
INSERT INTO EMP VALUES (7698,'BLAKE','MANAGER',7839,'01/05/1981',2850,NULL,30);
INSERT INTO EMP VALUES (7782,'CLARK','MANAGER',7839,'09/06/1981',2450,NULL,10);
INSERT INTO EMP VALUES (7566,'JONES','MANAGER',7839,'02/04/1981',2975,NULL,20);
INSERT INTO EMP VALUES (7654,'MARTIN','SALESMAN',7698,'28/09/1981',1250,1400,30);
INSERT INTO EMP VALUES (7499,'ALLEN','SALESMAN',7698,'20/02/1981',1600,300,30);
INSERT INTO EMP VALUES (7844,'TURNER','SALESMAN',7698,'08/09/1981',1500,0,30);
INSERT INTO EMP VALUES (7900,'JAMES','CLERK',7698,'03/12/1981',950,NULL,30);
INSERT INTO EMP VALUES (7521,'WARD','SALESMAN',7698,'22/02/1981',1250,500,30);
INSERT INTO EMP VALUES (7902,'FORD','ANALYST',7566,'03/12/1981',3000,NULL,20);
INSERT INTO EMP VALUES (7369,'SMITH','CLERK',7902,'17/12/1980',800,NULL,20);
INSERT INTO EMP VALUES (7788,'SCOTT','ANALYST',7566,'09/12/1982',3000,NULL,20);
INSERT INTO EMP VALUES (7876,'ADAMS','CLERK',7788,'12/01/1983',1100,NULL,20);
INSERT INTO EMP VALUES (7934,'MILLER','CLERK',7782,'23/01/1982',1300,NULL,10);

Règle

L'utilisation de cascade dans alter table disable constraint entraîne la désactivation des contraintes associées, sans suppression de données.

Vérification

SELECT constraint_name, constraint_type, search_condition, status
FROM user_constraints
WHERE table_name in('EMP', 'DEPT');

(noter la présence de status)
On voit que toutes les contraintes sont ENABLED.

ALTER TABLE emp DISABLE CONSTRAINT emp_empno_pk CASCADE;

SELECT constraint_name, constraint_type, search_condition, status
FROM user_constraints
WHERE table_name in('EMP', 'DEPT');

On voit que la désactivation de EMP_EMPNO_PK a aussi entraîné la désactivation de EMP_MGR_FK

Mais attention :
Si on fait :

ALTER TABLE emp ENABLE CONSTRAINT emp_empno_pk;

cela réactive seulement EMP_EMPNO_PK, mais pas EMP_MGR_FK

Cascade ne peut pas s'utiliser avec ALTER TABLE ENABLE CONSTRAINT.
Il faut donc réactiver une par une toutes les contraintes désactivées par l'option cascade.