Data Integirty

1.Constraints

 

type: Not null ,PK,FK,Check,Unique

 

SQL> create table mm(
  2  id number constraint con_pk primary key,
  3  name char(10) not null,
  4  uniqueid number unique,
  5  sal number check(sal>100));

 

  alter table mm  modify  name not null;

  alter table mm  add primary key(id);

  alter table mm  add unique(name);

  alter table mm  add check(sal>1000);

  alter table mm  add foreign key(id) references tt(id);

  alter table mm  add constraint  pk_cons primary key(id);

 

  alter table mm  drop primary key cascade;

  alter table mm  drop unique(name) cascade

  alter table mm  drop constraint pk_cons;

 

Constraint States:

 

■ ENABLE VALIDATE is the same as ENABLE. The constraint is checked and is
guaranteed to hold for all rows.
■ ENABLE NOVALIDATE means that the constraint is checked, but it does not have to
be true for all rows. This allows existing rows to violate the constraint, while
ensuring that all new or modified rows are valid.
In an ALTER TABLE statement, ENABLE NOVALIDATE resumes constraint checking
on disabled constraints without first validating all data in the table.
■ DISABLE NOVALIDATE is the same as DISABLE. The constraint is not checked and
is not necessarily true.
■ DISABLE VALIDATE disables the constraint, drops the index on the constraint,
and disallows any modification of the constrained columns. When any constraint is moved from the NOVALIDATE state to the VALIDATE state, all data must be checked. (This can be very slow.) However, moving from VALIDATE to NOVALIDATE simply forgets that the data was ever checked.Moving a single constraint from the ENABLE NOVALIDATE state to the ENABLE VALIDATE state does not block reads, writes, or other DDL statements. It can be done in parallel.

 

alter table mm enable validate constraint pk_con;

alter table mm disable novalidate primary key;

 

 

Constraint Check:

 

deferred: after commit,check;

nodeferred: immdeite checck;

 

set  constraints pk_emp deferred/immediate

alter session set constraints=deferred/immediate