Oracle Constraints

Way2DB | Constraints
Way2DB | Constraints

Related Articles

UNIQUE
PRIMARY KEY 
NOT NULL 
CHECK
FOREIGN KEY 

Self Referential Integrity

Other Articles

Adding constraints to the Existing Table

Dropping Constraints

Dropping a Constraint with out constraint name.

Constraint Codes

Disabling and Enabling Constraints

Precautions

Precautions to be taken while dealing with Primary key and Foreign Key

Precautions to be Taken while Adding Constraints Externally

Precautions to be taken while Dropping a Constraint

Integrity Constraints

  • Integrity constraints are used to 

               implement business rules, 
               to validate data, 
               to ensure data integrity.

  • Constraint means Rules.
  • Rules are used to Maintain Data Integrity
  • Constraints can be defined in 2 ways. 
                 a) Table Level
                 b) Column Level

  • Constraint Declaration Syntax: 

                a) Table Level
                      Constraint <constraint_name> <constraint_type> [columns]; 
                b) Column Level  
                      Constraint <constraint_name> <constraint_type> [condition];  
Integrity Constraints are of 3 types:

a) Entity Integrity
            UNIQUE
            PRIMARY KEY

b) Domain Integrity
             NOT NULL
             CHECK

c) Referential Integrity
             FOREIGN KEY

UNIQUE

  • It doesn't accept duplicate value.
  • It accepts "NULL" values

Example:
        empno number(4) UNIQUE;

Column Level Constraint Declaration:
       empno Number(4) UNIQUE,
       deptno Number(4) UNIQUE;

Table Level Constraint Declaration:
      empno Number(4), deptno Number(4), .....
      Constraint unq_empno UNIQUE( empno, deptno );

Primary Key

  • Primary Key accepts NULL's and Duplicate values.
  • It is the combination of UNIQUE and NOT NULL.
  • Using Primary Key column, we can UNIQUELY identify the records.
  • A table allows only 1 primary key.
  • Primary Key can be declared for a combination. This is called Composite Key.

Example:
       empno Number(4) Primary Key;
Column Level Constraint Declaration:
      empno Number(4) Primary Key;

Table Level Constraint Declaration:
     empno Number(4),........
     Primary Key (empno);


Note:  If the primary key is defined for combination of columns, it is called as "Composite Key".

Not Null

  • It doesn't accept NULL values.
  • It allows duplicate values.


Example:
       ename varchar2(20) NOT NULL;

Note:
      Table level constraint is not allowed for NOT NULL constraint.

Check

  • Check constraint is used when a business rule is based on Some condition.
  • It accepts NULL values


Example:
1. Sal NUMBER(7,2) CHECK ( sal>3000);
2. Gender CHAR(1) CHECK ( Gender IN ('M','F') );

Foreign Key

  • Foreign Key constraint is used to establish relation ship between 2 tables.
  • Values of Foreign Key should match with values of Primary Key or Unique Columns.
  • It allows "NULL" values 
  • It allows duplicates
  • Once a foreign key is declared, parent-child relationship is established between 2 tables.
  • This relationship is also called as 
                 Master / Detail relationship
                 Master / Transaction
                 Master / Slave
  • The table holding Primary key is called "Parent or Master"
        The table holding Foreign Key is called "Child or Detail or  
                   Transactions or Slave"
  • By default, Oracle creates one-many relationship for every parent-child relationship.
  • To establish one-to-one relationship, declare foreign key with UNQIUE constraints.

Examples:
      deptno number(4) references dept(deptno) UNIQUE;

Precautions to be taken while dealing with Primary key and Foreign Key

  • One Foreign Key can refer to One Primary Key, but can't refer to Multiple Primary Keys.
  • Two Foreign Key can refer to One Primary Key
  • A composite Primary Key must be referenced by a Composite Foreign Key.
next >>

Most Viewed

OCA Syllabus

OCA Practice Questions

OCA Practice Materials

OCA Exam Online 
""