LDD - Contraintes

Généralités

Nommer une contrainte sinon le serveur Oracle la nommerait en utilisant le format SYS_Cn.
Il est possible de créer une contrainte lors de la création de la table ou une fois que la table est créée.
Les contraintes peuvent être définies au niveau de la table ou au niveau d'une colonne.

Contrainte créée en même temps que la table :

CREATE TABLE [schema.]table(
    column datatype [DEFAULT expr] [column_constraint],
    ...
    [table_constraint]
    [,...]
);

CREATE TABLE emp(
    empno NUMBER(4),
    ename VARCHAR2(10),
    ...
    deptno NUMBER(2) NOT NULL,
    CONSTRAINT emp_empno_pk PRIMARY KEY(EMPNO)
);

Not null

Se définit au niveau de la colonne.

CREATE TABLE emp(
    ...
    deptno NUMBER(2) NOT NULL,
    ...
);

Mais il est possible de définir cette contrainte après la création de la table :

alter table emp modify deptno constraint emp_deptno_not_null not null;

ou encore (mais n'apparaîtra pas dans describe) :

alter table emp add constraint emp_deptno_not_null check(deptno is not null);

Unique

CONSTRAINT constraint_name UNIQUE(column_name));

CREATE TABLE dept(
    deptno NUMBER(2) UNIQUE,
    dname VARCHAR2(14),
    loc VARCHAR2(13)
);

CREATE TABLE dept(
    deptno NUMBER(2) constraint dept_deptno_unique UNIQUE,
    dname VARCHAR2(14),
    loc VARCHAR2(13)
);

CREATE TABLE dept(
    deptno NUMBER(2),
    dname VARCHAR2(14),
    loc VARCHAR2(13),
    CONSTRAINT dept_dname_uk UNIQUE(dname)
);

CREATE TABLE dept(
    deptno NUMBER(2),
    dname VARCHAR2(14),
    loc VARCHAR2(13)
);
ALTER TABLE dept ADD CONSTRAINT dept_dname_uk UNIQUE(dname);

Primary key

CONSTRAINT constraint_name PRIMARY KEY(column_name));

CREATE TABLE dept(
    deptno NUMBER(2),
    dname VARCHAR2(14),
    loc VARCHAR2(13),
    CONSTRAINT dept_deptno_pk PRIMARY KEY(deptno)
);

CREATE TABLE dept(
    deptno NUMBER(2) PRIMARY KEY,
    dname VARCHAR2(14),
    loc VARCHAR2(13)
);

CREATE TABLE dept(
    deptno NUMBER(2),
    dname VARCHAR2(14),
    loc VARCHAR2(13)
);
ALTER TABLE dept ADD CONSTRAINT dept_deptno_pk PRIMARY KEY(deptno);

Si la clé primaire est composée de plusieurs colonnes :

ALTER TABLE dept ADD CONSTRAINT dept_deptno_dname_pk PRIMARY KEY(dname, deptno);

Foreign key

CONSTRAINT constraint_name FOREIGN KEY(column_name)
    REFERENCES foreign_table(column_name)
    [ON DELETE CASCADE];

CREATE TABLE emp(
    empno NUMBER(4),
    ename VARCHAR2(10) NOT NULL,
    job VARCHAR2(9),
    mgr NUMBER(4),
    hiredate DATE,
    sal NUMBER(7,2),
    comm NUMBER(7,2),
    deptno NUMBER(7,2) NOT NULL,
    CONSTRAINT emp_deptno_fk FOREIGN KEY(deptno) REFERENCES dept(deptno)
);

CREATE TABLE emp(
    empno NUMBER(4),
    ename VARCHAR2(10) NOT NULL,
    job VARCHAR2(9),
    mgr NUMBER(4),
    hiredate DATE,
    sal NUMBER(7,2),
    comm NUMBER(7,2),
    deptno NUMBER(7,2) NOT NULL CONSTRAINT emp_deptno_fk REFERENCES dept(deptno)
);

CREATE TABLE emp(
    empno NUMBER(4),
    ename VARCHAR2(10) NOT NULL,
    job VARCHAR2(9),
    mgr NUMBER(4),
    hiredate DATE,
    sal NUMBER(7,2),
    comm NUMBER(7,2),
    deptno NUMBER(7,2) NOT NULL
);
ALTER TABLE emp ADD CONSTRAINT emp_deptno_fk FOREIGN KEY(deptno) REFERENCES dept(deptno);

Check

CONSTRAINT constraint_name CHECK(check_condition)

Exemple : encadrer les salaire.

ALTER TABLE emp
ADD CONSTRAINT emp_limite_salaire_chk
CHECK(sal > 500 and sal < 10000);

Exemples : limiter les valeurs possibles d'une colonne.

ALTER TABLE info_satisfaction
ADD CONSTRAINT satis_niveau_chk
CHECK(niveau_satisfaction in('SAT', 'INS', 'IND'));

ALTER TABLE EMP
ADD CONSTRAINT EMP_JOB_CK 
CHECK (JOB IN ('ANALYST','CLERK','MANAGER','PRESIDENT','SALESMAN'));