CONSTRAINTS

I tipi di Constraints supportati da Oracle sono i seguenti:
UNIQUE
NOT NULL
PRIMARY KEY (è l'unione della Unique e Not Null Constraint)
FOREIGN KEY (definita in una tabella figlio deve far riferimento a colonne della tabella padre di tipo Unique o Primary Key; può contenere valori NULL)
CHECK (definisce una condizione che ogni riga deve soddisfare)

R e g o l e

• può essere disabilitata anche se le colonne constraint contengono dati;
• una Tabella può contenere una sola Primary Key ma più Foreign Key;
• può essere definita sia a livello di colonna che di tabella. Le prime sono incluse nella definizione delle colonne mentre le seconde sono definite alla fine della definizione della tabella;
• la Constraint NOT NULL non può essere definita a livello di tabella (out of Line).

S i n t a s s i

a livello di colonna - in Line
column_name [CONSTRAINT constraint_name] constraint_type

a livello di tabella - out of Line
CONSTRAINT constraint_name constraint_type (column_name, ...)


E s e m p i o
Creare una tabella con due sole colonne EMPLOYEE_ID e MANAGER_ID di cui la prima sia una chiave primaria e l'altra una foreign key alla colonna EMPLOYEE_ID.

- livello di colonna
Create Table tmp_emp (
employee_id number(9) CONSTRAINT emp_id_pk PRIMARY KEY,
manager_id number(9) CONSTRAINT emp_man_id_fk REFERENCES tmp_emp(employee_id)
);
oppure
Create Table tmp_emp (
employee_id number(9)  primary key,
manager_id number(9)  references tmp_emp(employee_id)
);

- livello di tabella
Create table tmp_emp (
employee_id NUMBER(9), manager_id NUMBER(9),
CONSTRAINT emp_id_pk PRIMARY KEY (employee_id),
CONSTRAINT man_id_fk FOREIGN KEY (manager_id) REFERENCES tmp_emp(employee_id)
);

- misto
create table tmp_emp
(employee_id number(9),
manager_id number(9) CONSTRAINT emp_man_id_fk REFERENCES tmp_emp(employee_id),
CONSTRAINT emp_id_pk PRIMARY KEY (employee_id)
);

Se una FOREIGN KEY è definita senza specificare la colonna della tabella, il sistema in automatico la associa alla colonna chiave primaria della tabella.

E s e m p i o
Create Table tmp_emp
(employee_id number(9) CONSTRAINT emp_id_pk PRIMARY KEY,
manager_id number(9) CONSTRAINT emp_man_id_fk REFERENCES tmp_emp
);

Se si vede il codice Sql si scopre la seguente riga
....
CONSTRAINT "EMP_MAN_ID_FK" FOREIGN KEY ("MANAGER_ID")
 REFERENCES "SYSTEM"."TMP_EMP" ("EMPLOYEE_ID") ENABLE
....

Per ulteriori info vedere anche il post CONSTRAINT: DISABLE -ENABLE.


S i n t a s s i
aggiungere una costraint dopo la creazione della tabella

ALTER TABLE [schema.]NomeTabella ADD CONSTRAINT nome_constraint_pk PRIMARY KEY (nome_colonna);
ALTER TABLE [schema.]NomeTabella ADD CONSTRAINT nome_constraint_uk UNIQUE (nome_colonna);
ALTER TABLE [schema.]NomeTabella ADD CONSTRAINT nome_constraint_fk FOREIGN KEY (nome_colonna) REFERENCES tabella_referenziata (nome_colonna);
ALTER TABLE [schema.]NomeTabella ADD CONSTRAINT nome_constraint_fk CHECK  (espressione);

La seguente istruzione invece crea una Primary Key che utilizza un indice già esistente di tipo unique:
ALTER TABLE  [schema.]NomeTabella ADD CONSTRAINT nome_constraint_pk PRIMARY KEY (nome_colonna) USING INDEX nome_indice;  

Una CHECK Constraint contiene una espressione che sarà valutata vera o falsa. Una singola colonna può avere più Check constraints. Non è possibile usare:
- subquery
- la funzione SYSDATE, UID, USER, e USERENV
- riferimenti a CURRVAL, NEXTVAL, LEVEL, e alla pseudocolonna ROWNUM
- query che fanno riferimento a valori in altre righe.

Ad esempio utilizzando la funzione SYSDATE nella definizione di una tabella otteniamo il seguente errore:
CREATE TABLE emp_hired (
  employee_id number(9),
  hire_date date NOT NULL CONSTRAINT hire_date_max  CHECK (hire_date <= SYSDATE),
  CONSTRAINT EMP_HIRED_ID_PK  PRIMARY KEY (employee_id));

Errore SQL: ORA-02436: variabile data o di sistema specificata non correttamente nel vincolo CHECK
02436. 00000 -  "date or system variable wrongly specified in CHECK constraint"

Quando si aggiunge una nuova colonna ad una tabelle la constraint NOT NULL può essere applicata solo se la tabella non contiene dati.

ALTER TABLE nome_tabella ADD nome_colonna tipo_colonna NOT NULL;

Se la tabella contiene dati, occorre aggiungere un valore di default.

ALTER TABLE nome_tabella ADD nome_colonna tipo_colonna DEFUALT valore NOT NULL;

Se la colonna già esiste e si vuole aggiungere la constraint NOT NULL eseguire l'istruzione (in Line)

ALTER TABLE nome_tabella MODIFY nome_colonna NOT NULL;
opppure
ALTER TABLE nome_tabella MODIFY nome_colonna CONSTRAINT nome_constraint NOT NULL;

Il comando seguente invece termina in errore perché è come una dichiarazione out of Line.
ALTER TABLE nome_tabella ADD NOT NULL (nome_colonna);


La seguente VIEW visualizza le colonne associate ad una Constraint di una tabella per user:
SELECT * FROM USER_CONS_COLUMNS;

Per individuare le fk associate ad una tabella:

select c.owner , c.table_name, c.constraint_name

from ALL_CONSTRAINTS c 

where r_constraint_name 

in (

select constraint_name 

from all_constraints 

where  owner ='<schema>'

--and constraint_type <> 'R'

and table_name='<nometabella>'

)

and c.constraint_type ='R';