There are two ways to create constraints:
a) when creating a table – inline with the column,
b) after creating a table – more flexible.
create table hr.people ( id NUMBER(10) constraint people_id_pk PRIMARY KEY constraint people_id_ck CHECK (id >= 0), first_name VARCHAR2(30 CHAR) constraint people_fn_nn NOT NULL, last_name VARCHAR2(30 BYTE) constraint people_ln_ck CHECK (last_name is not null), local_hello NVARCHAR2(50) constraint people_lh_uq UNIQUE, height NUMBER(3,2) constraint people_height_ck CHECK (height >= 1.5) DISABLE, best_skill NUMBER(1) constraint people_bs_fk references skills(id) on delete cascade DEFERRABLE INITIALLY DEFERRED, knowledge_level NUMBER(1) constraint people_kl_fk references technology(id) on delete set null, hobby NUMBER(1) constraint people_hobby_fk references hobby(id) on delete set null ); alter table hr.people enable constraint people_height_ck; alter table hr.people add constraint people_fn_ck check (LENGTH(first_name) > 2); alter table hr.people drop constraint people_fn_ck; alter table hr.people modify local_hello constraint people_lh_nn NOT NULL;
When Primary Key or Unique constraint is created, index is build automatically.
Primary Key is combination of NOT NULL and UNIQUE constraints.
Not null constraint is translated to CHECK(column_name IS NOT NULL).
When Foreign Key is created index is not created, you should do it on your own as it is good practice.
Deferrable means that constraint is not checked directly in time of insert or update. It is checked at the end of the transaction. It can solve chicken and egg like problems.
On delete set null means that when parent of this value (foreign key relationship) is deleted value will be set to null.
On delete cascade means that row will be deleted when parent value is deleted.