Oracle Integrity Constraints

Data integrity allows to define certain data quality requirements that the data in the database needs to meet.
 If a user tries to insert data that doesn't meet these requirements,
 Oracle will not allow so.
Several types of Oracle constraints can be applied to Oracle tables to enforce data integrity.

Not Null:--------
This Oracle constraint is used to specify that a column may never contain a NULL value.
This is enforced at SQL insert and update time.
create table Test_notnull (
  a number not null,
  b number     ,
  c number
);

Unique Constraint: -----------------
This Oracle constraint is used to ensure that all column values within a table never contain a duplicate entry.
The unique constraint doesn't allow duplicate values in a column.
If the unique constraint encompasses two or more columns, no two equal combinations are allowed.
create table Test_unique (
  a number unique,
  b number
);

A unique constraint can be extended over multiple columns:
create table ri_3 (
  a number,
  b number,
  c number,
  unique (a,b)
);
Explicitly we are providing name to Unique constraint.
create table ri_3 (
  a number,
  b number,
  c number,
  constraint uq_ri_3 unique (a,b)
);

Primary Key Constraint:----------------------
Primary  Constraint is used to ensure that all column values within a table never contain duplicate and null values.
Primary key is combination of Notnull and Unique.
Table having only one primary key.
We can create primary key on multiple columns that is called composite primary key.
create table Test (
  a number primary key,
  b number
);
We can create primary key on multiple columns that is called composite primary key.
create table ri_primary_key_1 (
  a number,
  b number,
  c number,
  constraint pk_name primary key (a, b)
);

Check Constraint:
----------------
A check constraint allows to state a minimum requirement for the value in a column.
 If more complicated requirements are desired, an insert trigger must be used.

The following table allows only numbers that are between 100 and 1000 in the column empno;
create table Test1 (
  empno number check (a between 100 and 1000),
  sal number
);
Check constraints can be added after a table had been created:
alter table Test1
  add constraint CK_sal check (sal > 5000);
It is also possible to state a check constraint that check the value of more than one column.
The following example makes sure that the value of comm is smaller than the value of sal.
create table Test3 (
  empno number,
  sal     number,
  comm   number,
  check (comm < sal)
);

Foreign Key:-----------
A foreign key constraint (also called referential integrity constraint) on a column ensures that
 the value in that column is found in the primary key of another table.

 If a table has a foreign key that references a table,
 that referenced table can be dropped with a drop table .. cascade constraints.

It is not possible to establish a foreign key on a global temporary table.
If tried, Oracle issues a ORA-14455:

attempt to create referential integrity constraint on temporary table.
initially deferred-->after commiting it will effect
Initially Immediate->will effect immediatly

Here is an example of a single column foreign key constraint:
alter table emp  
add constraint fk_dept_no FOREIGN KEY (deptno)
references dept (dept_no)
initially deferred deferrable;

Here is an example of a multiple column foreign key constraint:
alter table cust_table
add constraint fk_cust_name FOREIGN KEY (person_name, person_gender)
references  person_table (person_name, person_gender)
initially deferred deferrable;

Deferring Constraint Checking:-----------------------------
Sometimes it is necessary to defer the checking of certain constraints,
 most commonly in the "chicken-and-egg" problem. Suppose we want to say:

CREATE TABLE chicken (cID INT PRIMARY KEY,
                      eID INT REFERENCES egg(eID));
CREATE TABLE egg(eID INT PRIMARY KEY,
                 cID INT REFERENCES chicken(cID));
But if we simply type the above statements into Oracle, we'll get an error.
 The reason is that the CREATE TABLE statement for chicken refers to table egg,
which hasn't been created yet! Creating egg won't help either, because egg refers to chicken.
To work around this problem, we need SQL schema modification commands.
 First, create chicken and egg without foreign key declarations:
CREATE TABLE chicken(cID INT PRIMARY KEY,
                     eID INT);
CREATE TABLE egg(eID INT PRIMARY KEY,
                 cID INT);

Then, we add foreign key constraints:

ALTER TABLE chicken ADD CONSTRAINT chickenREFegg
    FOREIGN KEY (eID) REFERENCES egg(eID)
    INITIALLY DEFERRED DEFERRABLE;
ALTER TABLE egg ADD CONSTRAINT eggREFchicken
    FOREIGN KEY (cID) REFERENCES chicken(cID)
    INITIALLY DEFERRED DEFERRABLE;
INITIALLY DEFERRED DEFERRABLE tells Oracle to do deferred constraint checking.

 For example, to insert (1, 2) into chicken and (2, 1) into egg,we use:
INSERT INTO chicken VALUES(1, 2);
INSERT INTO egg VALUES(2, 1);
COMMIT;

Because we've declared the foreign key constraints as "deferred",
they are only checked at the commit point. (Without deferred constraint checking,
we cannot insert anything into chicken and egg,
 because the first INSERT would always be a constraint violation.)
Finally, to get rid of the tables, we have to drop the constraints first,
because Oracle won't allow us to drop a table that's referenced by another table.

ALTER TABLE egg DROP CONSTRAINT eggREFchicken;
ALTER TABLE chicken DROP CONSTRAINT chickenREFegg;
DROP TABLE egg;
DROP TABLE chicken;

Foreign Keys with "set null on delete":
--------------------------------------
A foreign key with a "set null on delete" means that if a record in the parent table is deleted,
then the corresponding records in the child table will have the foreign key fields set to null.
The records in the child table will not be deleted.
A foreign key with a "set null on delete" can be defined in either a CREATE TABLE statement or an ALTER TABLE statement.
For example:
CREATE TABLE supplier
( supplier_id numeric(10) not null,
 supplier_name varchar2(50) not null,
 contact_name varchar2(50), 
 CONSTRAINT supplier_pk PRIMARY KEY (supplier_id)
);

CREATE TABLE products
( product_id numeric(10) not null,
 supplier_id numeric(10), 
 CONSTRAINT fk_supplier
   FOREIGN KEY (supplier_id)
  REFERENCES supplier(supplier_id)
  ON DELETE SET NULL
);

Using an ALTER TABLE statement-------------------------------
If we want to add a constraint to our new column we can use the following ALTER statement :
ALTER TABLE Test3 MODIFY(empno NOT NULL);
ALTER TABLE Test3
add constraint constraintname primary key (column name);

The syntax for creating a check constraint in an ALTER TABLE statement is:
ALTER TABLE table_name
add CONSTRAINT constraint_name CHECK (column_name condition) [DISABLE];

ALTER TABLE table_name
add CONSTRAINT constraint_name
  FOREIGN KEY (column1, column2, ... column_n)
  REFERENCES parent_table (column1, column2, ... column_n)
  ON DELETE SET NULL;

How to Enable/Disable constraints:
---------------------------------
Syntax:
-------
SQl>Alter table tablename
    enable constraint constraint_name;
SQl>Alter table tablename
    Disable constraint constraint_name;

How to Check the Constraint details:
-----------------------------------
SQL> SELECT constraint_name FROM user_constraints WHERE table_name = 'TEST'
     AND constraint_type = 'PK_NAME';

How to Drop constraint:
----------------------
ALTER TABLE table_name
drop CONSTRAINT constraint_name;

Ur's
AmarAlam