FAQ

1. What are the different types of Integrity Constraints available in RDBMS ?
A. There are 3 types of Integrity Constraints (IC), they are
    a) Entity Integrity Constraint
        - PRIMARY KEY
        - UNIQUE
    b) Domain Integiry Constraint
        - NOT NULL
        - CHECK
    c) Referential Integrity Constraint
        - FOREIGN KEY

2. What does Self referential Integrity means ?
A. Self referential Integrity means, A foreign Key of a table is refering to Primary Key of the same table.
    ex: EMP
        empno, ename, job, sal, MGR, deptno.
            Foreign Key ( MGR ) CONSTRAINT fk_mgr REFERENCES emp( empno )

    Here MGR can contain either EMPNO or NULL.
    This kind of relationship is called as Self Referential Integrity.

3. What is the use of default keyword ?
A. Default key is used to insert default values in place of NULL.
    ex: create table emp
        ( empno number(4) primary key,
          ename varchar2(20) unique,
          sal number(7,2) check (sal > 2000)   default 0,
          hiredate date   default sysdate,
          ..... );

    Here when the user doesn't provide hiredate, instead of NULL, sysdate will be stored as Hiredate.
    And when sal <=2000, instead of NULL, 0 will be stored.

    This improves performance, because
    4+ NULL = NULL,
    but 4+0 =4.

4. How many ways can we declare constraints ?
A. Constraint can be decalred in 2 ways,
    (i) Column Level
    (ii) Table Level.

5. When should we use Column Level or Table Level ?
A.  Column Level Constraints:
    - Use column level constraints to declare a constraint for a single column.
    - Here constraints are declared immediately after declaring the column.
    - we can't combine multiple columns for a single constraint.

     Table Level Constraints:
    - Use table level constraints to declare a constraint for combination of columns.
    - Table level constraints are declared after declaring all the columns.

    Examples:

    (i) Column Level Constraints.
        Table Name:    ACCT_MASTER
        Columns:        ACCNO, ACC_NAME, BALANCE.
        Rules:       
            a) Accno should not be repeated and should not be NULL.
            b) Name should not be NULL.
            c) Balance > 1000 and should not be NULL.

        Create table ACCT_MASTER
        (
            ACCNO  number(4) PRIMARY KEY,
            ACC_NAME  varchar2(20) NOT NULL,
            BALANCE number(7,2) CHECK (BALANCE > 1000) NOT NULL
        );

    (ii) Table Level Constraints.
        Table Name:    REGISTRATION
        Columns:        STUDENT_NO, COURSE_ID, DATE_OF_COMPLETION.
        Rules:
            a) A record can be identified using STUDENT_NO, COURSE_ID. Because a student can do multiple courses.
            b) DATE_OF_COMPLETION must not be null. If no input is provided, insert system date.

        Create table REGISTRATION
        (
            STUDENT_NO number(4),
            COURSE_ID number(4),
            DATE_OF_COMPLETION date DEFAULT sysdate,
            PRIMARY KEY (STUDENT_NO, COURSE_ID)
        );

6. Which constraint is not possibel at Tabel Level ?
A. NOT NULL is not possible.

7. Discuss about Primary Key - Foreign Key relation ship ?
A.    - One Foreign Key can refer to One Primary Key, but not multiple Primary Key's.
    - Two or More Foreign Keys can refer to One Primary Key.
    - A Composite Primary Key must be referenced by Composite Foreign Key only.

8. What are the Precautions to be taken before adding a constraint externally ?
A.    - Primary Key can't be added to a column that contains NULL's or Duplicate records.
    - To Add a check constraint, ensure the Rules is being followed by the column.
        ex: Check ( sal > 3000 ).
            - Ensure that none of the sal <=3000. Else the Constraint will be failed ( Voilated ).
            - But we can aviod it using " NOVALIDATE ", this can be used only with Check constraint.
            - This doesn't effect the existing data, but check only for the Future data.
   
    =>    CHECK ( SAL > 3000 )  NOVALIDATE;

9. What are the Precautions to be taken before dropping a constraint ?
A.    - Before dropping Primary Key, Drop its respective Foreign Key also.
    - Even a table cannot be dropped if it is participating in a Primary Key - Foreign Key relation ship.
    - Even a table cannot be truncate if it is participating in a Primary Key - Foreign Key relation ship.

    - We can use " CASCADE " keyword to achieve these operations.

    Syntax:
        Alter table <table_name> DROP CONSTRAINT <constraint_name> CASCADE;
        or
        DROP table <table_name> CONSTRAINT CASCADE;
   
    Ex:
        (i) Alter table DEPT drop PRIMARY KEY CASCADE;
        (ii) Drop table emp CASCADE CONSTRAINTS;
   
10. How can we drop a constraint with out Constraint Name ?
A.    Oracle provides a table called " USER_CONSTRAINTS ", it maintains complete information about constraints declared in all the table.
    By default, Oracle provides names for each constraint.

    Syntax:
        Select constraint_name, constraint_type from USER_CONSTRAINTS
        where table_name = <table_name>
   
    CONSTRAINT CODES used in Oracle are
        Primary Key    P
        Foreign Key    R
        Unique        U
        Check        C
        Not Null        C

    Examples:
        Select constraint_name, constraint_type,constraint_code from USER_CONSTRAINTS
        where table_name = 'EMP';
   
    - Get the constraint name and drop it.

11. How to Disable / Enable Constraints ?
A.    - The constraints are disabled and enabled when there is huge amount of data to be copied from one table to another table.
    - If constraint is disabled, it still exists in the database but it will not work till it is enabled.
    - Copying data with constraints consume more time. Hence first we disable the constraints the ccopy the data, then enable the constraints.
        This must be done only when the data is valid data.

    Syntax:
        Alter table <table_name>  disable / enable Constraint <constraint_name>
   
    Examples:
        (i)    Enabling Primary Key:
                Alter table emp enable primary key;

                    - But ensure that there are no duplicate records and no NULL values in the column.

            Disable primary key:
                Alter table emp disable primary key.

                    - Primary key can't be disabled if it is referenced by any foreign key.
                    - Hence in this case we need to use CASCADE operation.

                Alter table emp disable primary key CASCADE;
           
                    - When primary key is enabled, the dependent foreign keys are not enabled automatically. Hence they must be done manually.
       
        (ii)    Enabling Check constraint:
                Alter table emp enable constraint ck_sal;
       
                - To Enable a check constraint,
                    - Ensure that none of record voilates the integrity rules
                    - But we can aviod it using " NOVALIDATE ", this can be used only with Check constraint.
                    - This doesn't effect the existing data, but check only for the Future data.
       
                Alter table emp enable NOVALIDATE constraint ck_sal;

            Disable Check constraint:
                Alter table emp disable constraint ck_sal;
           
        (iii)    Enabling Unique:
                Alter table emp enable constraint unique_ename;
           
                    - Ensure that there are no null records placed in column.
               
            Disabling Unique:
                Alter table emp disable constraint unique_ename;
               
                    - Unique constraint can't be disabled if it is referenced by any foreign key.
                    - Hence in this case we need to use CASCADE operation.

                Alter table emp disable constraint ck_sal CASCADE;
           
                    - When Unique constraint is enabled, the dependent foreign keys are not enabled automatically. Hence they must be done manually.

        (iv)    Enabling Foreign Key:
                Alter table emp enable constraint fk_deptno;
           
                    - Ensure that there are no values which doesn't exists in parent table.
           
            Disable Foreign Key:
                Alter table emp disable constraint fk_deptno;