PLSQL – constraints

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s