Les contraintes sont des règles de gestion, qui doivent (normalement) être vérifiées lors des mises ? jour (INSERT, UPDATE, DELETE).
Ces contrôles sont centralisés dans la base, au niveau de la structure de la table elle même et non pas déportées sur l’application cliente.
Une mise ? jour qui ne satisfait pas une contrainte, déclenche une erreur d’exécution :’…CONSTRAINT VIOLATED…’
Dans certains cas les contraintes peuvent être momentanément invalidées (DISABLEd ou DEFERRED).
Il y a plusieurs types de contraintes :
- NULL : valeur indéfinie (??)
- NOT NULL : valeur obligatoire (non indéfinie)
- DEFAULT : une valeur par defaut si non renseignée
- UNIQUE : !
- PRIMARY KEY : clé primaire (identifiant de la table, obligatoire ET unique)
- FOREIGN KEY : clé étrangère ‘colonne qui référence la clé primaire d’une autre table)
- CHECK : doit vérifier une condition
On peut créer des contraintes directement avec l’instruction ‘CREATE TABLE’ ou en ajouter / modifier / supprimer, respectivement avec les instructions ‘ALTER TABLE ADD | MODIFY |DROP CONSTRAINT …’
exemples de contraintes
– exemple de table avec des colonnes …
– et des contrainte NOT NULL, UNIQUE, PRIMARY KEY et CHECK
SQL> DROP TABLE employe_dd;
SQL> CREATE TABLE employe_dd
( employee_id NUMBER(6) PRIMARY KEY
, last_name VARCHAR2(25) NOT NULL
, salary NUMBER(8,2)
, CONSTRAINT emp_salary_min_dd CHECK (salary > 0)
, email VARCHAR2(25) NOT NULL
, CONSTRAINT emp_email_dd UNIQUE (email)
, commission_pct NUMBER(2,2)
, department_id NUMBER(4)
) ;
– exemple de table avec clé primaire et étrangère NOMMEES rajoutées ? posteriori
– ici la contrainte NOT NULL est nommée…
SQL> DROP TABLE departement_dd;
SQL> CREATE TABLE departement_dd
( department_id NUMBER(4)
, department_name VARCHAR2(30) CONSTRAINT dept_name_dd_nn NOT NULL
, location_id NUMBER(4)
) ;
SQL> ALTER TABLE departement_dd
ADD ( CONSTRAINT dept_id_dd_pk PRIMARY KEY (department_id));
SQL> ALTER TABLE employe_dd
ADD ( CONSTRAINT emp_dept_dd_fk FOREIGN KEY (department_id)
REFERENCES departement_dd );
Note : la contrainte d’intégrité référentielle impose qu’un employé appartienne a un département EXISTANT.
Symétriquement, si l’on veut supprimer un département qui a des employes on viole la contrainte :
SQL> delete from departments where department_id=10;
–> erreur : ORA-02292: violation de contrainte (HR.EMP_DEPT_FK) d’intégrité - enregistrement fils existant
Il existe une clause ‘ON DELETE CASCADE’ qui permet de déclencher une suppression automatique des lignes ‘filles’ si une ligne ‘mère’ est supprimée : supprimer par exemple tous les emplyés associés ? un departement qui vient d’être supprimé…
SQL> ALTER TABLE employees
ADD ( CONSTRAINT emp_dept_fk FOREIGN KEY (department_id)
REFERENCES departments ON DELETE CASCADE )
SQL> select count(*) from employees
WHERE department_id=100;
–> 6
SQL> DELETE from departments WHERE department_id=100;
–> 1 row deleted
mais les lignes de ‘employees’ correspondantes ont également été supprimées :
SQL> select count(*) from employees
WHERE department_id=100;
–> 0
Infos dans le référentiel
SQL> select * from user_constraints
ou + précisément
SQL> select constraint_name, constraint_type, table_name,
search_condition,r_constraint_name, status
from user_constraints
where table_name like ‘%DD’
CONSTRAINT_NAME CONSTRAINT_TYPE TABLE_NAME SEARCH_CONDITION R_CONST_NAME STATUS
—————– ————— ———- —————- ———— ——
EMP_SALARY_MIN_DD C EMPLOYE_DD salary > 0 - ENABLED
SYS_C004030 C EMPLOYE_DD EMAIL IS NOT NULL - ENABLED
SYS_C004029 C EMPLOYE_DD LAST_NAME IS NOT NULL - ENABLED
DEPT_NAME_DD_NN C DEPARTEMENT_DD D_NAME IS NOT NULL - ENABLED
EMP_DEPT_DD_FK R EMPLOYE_DD - DEPT_ID_DD_PK ENABLED
SYS_C004032 P EMPLOYE_DD - - ENABLED
EMP_EMAIL_DD U EMPLOYE_DD - - ENABLED
DEPT_ID_DD_PK P DEPARTEMENT_DD - - ENABLED
invalidation de contraintes
Les clauses ‘DEFERRED’ et ‘DEFERRABLE’ permettent de différer l’application de la contrainte ? la fin de la
transaction :
SQL> create table test(n number check ( n in (10, 20) ) deferrable initially deferred );
SQL> alter session set constraint = deferred;
SQL> insert into test values(100);
–> OK
SQL> insert into test values(200);
SQL> commit;
–> erreur sur la transaction
ORA-02091: transaction annulée
ORA-02290: violation de contraintes (HR.SYS_C004039) de vérification
SQL> alter session set constraint = immediate;
SQL> insert into test values (100);
–> erreur sur l’insertion :
ORA-02290: violation de contraintes (HR.SYS_C004039) de vérification
On peut également utiliser les clause ENABLE/DISABLE de ‘ALTER TABLE’
ENABLE VALIDATE : s’applique aux lignes existantes dans la table
ENABLE NOVALIDATE : aux ligne futures
DISABLE : inhibe la contrainte
SQL> CREATE TABLE test (n number , constraint nb_ok check ( n in (10, 20) ));
SQL> insert into test values (100);
– erreur : ORA-02290: violation de contraintes (HR.NB_OK) de vérification
SQL> ALTER TABLE test DISABLE CONSTRAINT nb_ok ;
SQL> insert into testing values(100);
–> 1 ligne insérée (la contrainte était inhibée)
ALTER TABLE test ENABLE NOVALIDATE CONSTRAINT nb_ok;
–> OK : Table Altered…
ALTER TABLE test ENABLE VALIDATE CONSTRAINT nb_ok;
–> erreur : ORA-02293: impossible de valider (HR.NB_OK)
- violation d’une contrainte de contrôle
la clause validate, force le controle de la contraine sur la lignes existantes et la valeur ‘100′ insérée précédemment est non conforme…
Voici un exemple complet de création de mini base avec contraintes, d’après les tables de démo HR officielles d’Oracle :
Rem
Rem $Header: hr_cre.sql 29-aug-2002.11:44:03 hyeh Exp $
Rem Copyright (c) 2001, 2002, Oracle Corporation. All rights reserved.
Rem
REM ****************************************************
REM Create the REGIONS table to hold region information for locations
REM HR.LOCATIONS table has a foreign key to this table.
Prompt ****** Creating REGIONS table ….
CREATE TABLE regions
( region_id NUMBER
CONSTRAINT region_id_nn NOT NULL
, region_name VARCHAR2(25)
);
CREATE UNIQUE INDEX reg_id_pk
ON regions (region_id);
ALTER TABLE regions
ADD ( CONSTRAINT reg_id_pk
PRIMARY KEY (region_id)
) ;
REM ********************************************************
REM Create the COUNTRIES table to hold country information for customers
REM and company locations.
REM OE.CUSTOMERS table and HR.LOCATIONS have a foreign key to this table.
Prompt ****** Creating COUNTRIES table ….
CREATE TABLE countries
( country_id CHAR(2)
CONSTRAINT country_id_nn NOT NULL
, country_name VARCHAR2(40)
, region_id NUMBER
, CONSTRAINT country_c_id_pk
PRIMARY KEY (country_id)
)
ORGANIZATION INDEX;
ALTER TABLE countries
ADD ( CONSTRAINT countr_reg_fk
FOREIGN KEY (region_id)
REFERENCES regions(region_id)
) ;
REM ********************************************************
REM Create the LOCATIONS table to hold address information for company departments.
REM HR.DEPARTMENTS has a foreign key to this table.
Prompt ****** Creating LOCATIONS table ….
CREATE TABLE locations
( location_id NUMBER(4)
, street_address VARCHAR2(40)
, postal_code VARCHAR2(12)
, city VARCHAR2(30)
CONSTRAINT loc_city_nn NOT NULL
, state_province VARCHAR2(25)
, country_id CHAR(2)
) ;
CREATE UNIQUE INDEX loc_id_pk
ON locations (location_id) ;
ALTER TABLE locations
ADD ( CONSTRAINT loc_id_pk
PRIMARY KEY (location_id)
, CONSTRAINT loc_c_id_fk
FOREIGN KEY (country_id)
REFERENCES countries(country_id)
) ;
Rem Useful for any subsequent addition of rows to locations table
Rem Starts with 3300
CREATE SEQUENCE locations_seq
START WITH 3300
INCREMENT BY 100
MAXVALUE 9900
NOCACHE
NOCYCLE;
REM **************************************************
REM Create the DEPARTMENTS table to hold company department information.
REM HR.EMPLOYEES and HR.JOB_HISTORY have a foreign key to this table.
Prompt ****** Creating DEPARTMENTS table ….
CREATE TABLE departments
( department_id NUMBER(4)
, department_name VARCHAR2(30)
CONSTRAINT dept_name_nn NOT NULL
, manager_id NUMBER(6)
, location_id NUMBER(4)
) ;
CREATE UNIQUE INDEX dept_id_pk
ON departments (department_id) ;
ALTER TABLE departments
ADD ( CONSTRAINT dept_id_pk
PRIMARY KEY (department_id)
, CONSTRAINT dept_loc_fk
FOREIGN KEY (location_id)
REFERENCES locations (location_id)
) ;
Rem Useful for any subsequent addition of rows to departments table
Rem Starts with 280
CREATE SEQUENCE departments_seq
START WITH 280
INCREMENT BY 10
MAXVALUE 9990
NOCACHE
NOCYCLE;
REM *********************************************
REM Create the JOBS table to hold the different names of job roles within the company.
REM HR.EMPLOYEES has a foreign key to this table.
Prompt ****** Creating JOBS table ….
CREATE TABLE jobs
( job_id VARCHAR2(10)
, job_title VARCHAR2(35)
CONSTRAINT job_title_nn NOT NULL
, min_salary NUMBER(6)
, max_salary NUMBER(6)
) ;
CREATE UNIQUE INDEX job_id_pk
ON jobs (job_id) ;
ALTER TABLE jobs
ADD ( CONSTRAINT job_id_pk
PRIMARY KEY(job_id)
) ;
REM ***********************************************
REM Create the EMPLOYEES table to hold the employee personnel
REM information for the company.
REM HR.EMPLOYEES has a self referencing foreign key to this table.
Prompt ****** Creating EMPLOYEES table ….
CREATE TABLE employees
( employee_id NUMBER(6)
, first_name VARCHAR2(20)
, last_name VARCHAR2(25)
CONSTRAINT emp_last_name_nn NOT NULL
, email VARCHAR2(25)
CONSTRAINT emp_email_nn NOT NULL
, phone_number VARCHAR2(20)
, hire_date DATE
CONSTRAINT emp_hire_date_nn NOT NULL
, job_id VARCHAR2(10)
CONSTRAINT emp_job_nn NOT NULL
, salary NUMBER(8,2)
, commission_pct NUMBER(2,2)
, manager_id NUMBER(6)
, department_id NUMBER(4)
, CONSTRAINT emp_salary_min
CHECK (salary > 0)
, CONSTRAINT emp_email_uk
UNIQUE (email)
) ;
CREATE UNIQUE INDEX emp_emp_id_pk
ON employees (employee_id) ;
ALTER TABLE employees
ADD ( CONSTRAINT emp_emp_id_pk
PRIMARY KEY (employee_id)
, CONSTRAINT emp_dept_fk
FOREIGN KEY (department_id)
REFERENCES departments
, CONSTRAINT emp_job_fk
FOREIGN KEY (job_id)
REFERENCES jobs (job_id)
, CONSTRAINT emp_manager_fk
FOREIGN KEY (manager_id)
REFERENCES employees
) ;
ALTER TABLE departments
ADD ( CONSTRAINT dept_mgr_fk
FOREIGN KEY (manager_id)
REFERENCES employees (employee_id)
) ;
Rem Useful for any subsequent addition of rows to employees table
Rem Starts with 207
CREATE SEQUENCE employees_seq
START WITH 207
INCREMENT BY 1
NOCACHE
NOCYCLE;
REM ***********************************************
REM Create the JOB_HISTORY table to hold the history of jobs that
REM employees have held in the past.
REM HR.JOBS, HR_DEPARTMENTS, and HR.EMPLOYEES have a foreign key to this table.
Prompt ****** Creating JOB_HISTORY table ….
CREATE TABLE job_history
( employee_id NUMBER(6)
CONSTRAINT jhist_employee_nn NOT NULL
, start_date DATE
CONSTRAINT jhist_start_date_nn NOT NULL
, end_date DATE
CONSTRAINT jhist_end_date_nn NOT NULL
, job_id VARCHAR2(10)
CONSTRAINT jhist_job_nn NOT NULL
, department_id NUMBER(4)
, CONSTRAINT jhist_date_interval
CHECK (end_date > start_date)
) ;
CREATE UNIQUE INDEX jhist_emp_id_st_date_pk
ON job_history (employee_id, start_date) ;
ALTER TABLE job_history
ADD ( CONSTRAINT jhist_emp_id_st_date_pk
PRIMARY KEY (employee_id, start_date)
, CONSTRAINT jhist_job_fk
FOREIGN KEY (job_id)
REFERENCES jobs
, CONSTRAINT jhist_emp_fk
FOREIGN KEY (employee_id)
REFERENCES employees
, CONSTRAINT jhist_dept_fk
FOREIGN KEY (department_id)
REFERENCES departments
) ;
