Constraints Maintenance

Adding Constraints to a table:
Ø  A constraint can be added to a table at any time after the table was created by using alter table statement, using ADD clause.
Syntax:
            Alter Table <TableName>
            Add [Contraint <ConstraintName>]
            Cons_type(column_name);
The Constraint name in the syntax is optional, but recommended.
Guidelines:
Ø  We can ADD, DROP, ENABLE or DISABLE a constraint, but cannot modify the physical structure of the table.
Ø  A NOT NULL can be added to existing column by using the modify clause of the ALTER table statement.
Ø  NOT NULL can be defined only when the table contains no rows.
Example:
·         Alter Table Emp Add Constraint Emp_mgr_fk Foreign key(mgr) References Emp(Empno);
Dropping Constraints:
Ø  To Drop a constraint identity the constraint the constraint name from the
o   USER_CONSTRAINTS
o   USER_CONS_COLUMNS data dictionary views.
Ø  The ALTER TABLE statement is used with the drop clause.
Ø  The CASCADE option of the DROP Clause causes any dependent constraints also to be dropped.
Ø  When a constraint is dropped, the constraint is no longer enforced and is no longer available in the data dictionary.
Syntax:
·         Alter Table <Table_Name>

DROP
            PRIMARY KEY/UNIQUE (column)/
            CONSTRAINT ConstraintName[CASCADE];
Example:
·         Alter Table Dept drop primary key cascade;
·         Alter Table Emp drop constraint Emp_Mgr_FK;
Enabling Constraints:
Ø  The constraint can be enabled without dropping in or re-creating it.
Ø  The alter table statement with the enable clause is used for the purpose.
Syntax:
·         Alter Table <TableName>
                  Enable Constraint <ConstraintName>;
Guidelines:
Ø  Enabling a constraint applies to all the data in the table at a time.
Ø  When an UNIQUE or Primary key constraint is ENABLED, the UNIQUE or Primary key index is automatically created.
Ø  The ENABLE clause can be used both in create table as well as alter table statements.
Examples:
·         Alter Table Emp ENABLE Constraint Emp_Empno_FK;
Viewing Constraints:
Ø  To view all constraints on a table query upon the data dictionary USER_CONSTRAINTS.
Ø  The Codes that are revealed are …
            C à CHECK
            P à PRIMARY KEY
            R à REFERENTIAL INTEGRITY
            U à UNIQUE KEY
Example:
·         Select Constraint_name, Constraint_type,Search_condtion From USER_CONSTRAINTS where table_name = ‘EMP’;
Viewing the columns Associated with Constraints:
Ø  The Names of the columns that are involved in constraints can be known by querying the USER_CONS_COLUMNS data dictionary view.
Example:

·         Select  constraint_name, column_name from user_cons_columns where table_name =’EMP’;