Dropping of a Constraint (Scenarios to Drop a Constraint)

Alter table <table_name> drop constraint <constraint_name>;

Although constraints are used to validate the data entered into the database, in certain situations, it would be more efficient if the constraints were temporarily disabled until a particular function has been performed.  In yet another situation, it might be necessary to forgo validating the data entered into the database.  For such situations Oracle provides the various constraint states, which can be used in combination as well.  These states of the constraints can be set either during the table creation or the table can be altered later to modify them.

SCENARIO 1:

Suppose we decide to shift the data of a child table to another database, we cannot do that as it has links with the parent table of this database.  Hence data cannot be moved from the child table alone.  To facilitate this we disable the foreign key for the duration of the shift. And enable it again to establish the link again.  The command for disabling a key is,

Alter Table <Table_Name> Modify Constraint <Constraint_Name> Disable;

Alter Table <Table_Name> Modify Constraint <Constraint_Name> Enable;

See the following commands.

Select Constraint_Name,Constraint_Type, Status From User_Constraints Where Table_Name='EMP';

CONSTRAINT_NAME                   C STATUS

------------------------------ -    -------

PK_EMP                                P ENABLED

FK_DEPTNO                             R ENABLED

Alter Table Emp Modify Constraint SYS_C00253425 Disable;

Table altered.

Select Constraint_Name,Constraint_Type, Status From User_Constraints Where Table_Name='EMP';

Alter Table Emp Modify Constraint SYS_C00253425 Enable;

Table altered.

Select Constraint_Name,Constraint_Type, Status From User_Constraints Where Table_Name='EMP';

CONSTRAINT_NAME                       C STATUS

------------------------------        - --------

PK_EMP                                 P ENABLED

FK_DEPTNO                              R ENABLED

SCENARIO 2:

Now consider another situation, where huge amount of data is being moved from one table to another.  The data present in the old table are valid and conform to the primary key in one of its columns (self referential).  When the data enters the new table, it will undergo validation as per the primary key in its column.  As the data is already a validated data we can disable the foreign key and after the shift of the data we enable it once again, to avoid the unnecessary waste of time by way of overhead, while rechecking.

You can read more about constraints here Types of Constraint States used in ETL