1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Oracle Referential Integrity

Discussion in 'SQL PL/SQL' started by SBH, Nov 15, 2010.

  1. SBH

    SBH Forum Expert

    Messages:
    99
    Likes Received:
    6
    Trophy Points:
    160
    Location:
    Bangalore
    1. Overview

    Data integrity must be maintained to ensure its quality, consistency and non redundancy within the database. Constraints are the rules enforced upon the data to maintain data integrity, violation of which results in process abortion from Oracle server. Integrity constraints can be broadly classified as below.
    1. Referential Integrity – Foreign Key
    2. Entity Integrity – Unique constraint and Primary Key
    3. Domain Integrity – Check Constraint

    In this article, we shall discuss more on Referential Integrity, while the rest would be covered in upcoming articles.

    2. Referential Integrity

    Referential integrity is fundamental property of relational data, which means that a column value always refers to another column value. Both columns can reside in same table or different tables. The referred column is known as Parent key, while the one referring to it is Child or Foreign Key in its table. This implies that Foreign Key constraint ensures referential integrity of the data in an Oracle database.
    Parent key must be the primary key of the table. Parent Key and Foreign Key share One to Many relationship i.e. a Parent Key can have multiple references. The scope of the foreign key values is the value bracket defined by Parent key. If any value (other than NULL) other than Parent key values is found in the foreign key, Oracle server raises the exception ORA-00291(integrity constraint (TEST.EMP_ADD_FK) violated - parent key not found)

    Syntax

    Column Level:

    Code (Text):
    COLUMN [data type] [CONSTRAINT] [constraint name] [REFERENCES] [table name (column name)]
    Table level:

    Code (Text):
    CONSTRAINT [constraint name] [FOREIGN KEY (foreign key column name) REFERENCES] [referenced table name (referenced column name)]
    Example,

    Code (SQL):
    CREATE TABLE DEPARTMENT
    (DEPARTMENT_ID NUMBER PRIMARY KEY,
     DEPARTMENT_NAME VARCHAR2(100),
     LOCATION_ID NUMBER)

    CREATE TABLE EMPLOYEE
    (EMPLOYEE_ID NUMBER PRIMARY KEY,
     EMPLOYEE_NAME VARCHAR2(100),
    DEPARTMENT_ID NUMBER CONSTRAINT FK_DEPT FOREIGN KEY REFERENCES DEPARTMENT(DEPARTMENT_ID),
    SALARY NUMBER,
    JOB VARCHAR2(100),
    HIRE_DATE DATE)
    Above scenario is demonstrated in the below figure. It also shows the violation of Referential Integrity during INSERT operation.

    [​IMG]

    It can also be added after table creation using ALTER TABLE command.

    Code (SQL):
    ALTER TABLE EMPLOYEE
    ADD CONSTRAINT DEPT_FK
    FOREIGN KEY (DEPARTMENT_ID)
    REFERENCES DEPARTMENT(DEPARTMENT_ID)
    If a foreign key exist for a Parent key, Oracle server restricts the deletion of Parent key record by raising the exception ORA-00292(integrity constraint (TEST.EMP_ADD_FK) violated - child record found).
    Oracle provides two cascading options to handle the above default behavioral situation.

    i. ON DELETE CASCADE

    Delete operation is cascaded from Parent record to Child record. Implication is that whenever the Parent record is deleted, all referring child records are also deleted.
    Usage of ON DELETE CASCADE clause

    Code (SQL):
    CREATE TABLE TEST
    (ccode varchar2(5)
         CONSTRAINT TEST_FK REFERENCES PARENT_TEST (ccode)
                   ON DELETE CASCADE,
       ...
    );
    ii. ON DELETE SET NULL

    Delete operation is cascaded from Parent record to Child record by setting the foreign key column to NULL. Example,

    Code (SQL):
    CREATE TABLE TEST
    (ccode varchar2(5)
         CONSTRAINT TEST_FK REFERENCES PARENT_TEST (ccode)
                   ON DELETE SET NULL,
       ...
    );
    Please note that Foreign Key constraint can only be enforced on Physical table and not on the Temporary table. Any such attempt would raise the exception ORA-14455: attempt to create referential integrity constraint on temporary table.

    3. DROP Foreign Key

    Foreign key constraint can be dropped using ALTER table command. Syntax,

    Code (Text):
    ALTER TABLE [TABLE NAME]
    DROP CONSTRAINT [CONSTRAINT NAME]
    Example,

    Code (SQL):
    ALTER TABLE EMPLOYEE
    DROP CONSTRAINT DEPT_FK
     

    Attached Files: