How to Disable and Enable Constraints in Oracle

How to Disable and Enable Constraints in Oracle with Examples

In this article, I am going to discuss How to Disable and Enable Constraints in Oracle with Examples. Please read our previous article where we discussed How to Rename a constraint in Oracle with Examples. Here, I am going to give the example using the CHECK constraint but the same is applicable to all other types of constraints.

How to disable and enable constraints in oracle?

By default, constraints are in enable mode. If you want to disable constraints temporarily, then you need to use the “DISABLE” keyword. It means that constraint is existing in the database but does not work till it make as “ENABLE”.

Whenever we want to copy a huge amount of data from one table to another table there we use the “DISABLE” keyword. The following is the syntax to enable and disable Constraints in Oracle.

Syntax: ALTER TABLE <TN> DISABLE / ENABLE CONSTRAINT <CONSTRAINT KEY NAME>;

Example:

Let us first create a table to understand how to Disable and Enable constraints in Oracle. Please execute the below SQL script to create a table.

CREATE TABLE TEST15(Name VARCHAR2(10), Salary NUMBER (10) CONSTRAINT CHK_SALARY CHECK (Salary >= 5000));

Now, try to execute the below INSERT statement.

INSERT INTO TEST15 VALUES ('Anurag', 5000);

When you execute the above query, it is executed successfully. This is because we are inserting 5000 as the value to the Salary column and the check constraint verify the value and the validation is successful and hence it allowed the data to be inserted. Now, try to execute the below INSERT statement.

INSERT INTO TEST15 VALUES ('Sambit', 3000);

When you try to execute the above query, you will get an error as shown in the below image. This is because we are inserting 3000 as the value to the Salary column and the check constraint verify the value and the validation Failed and hence it is throwing an error.

How to Disable and Enable Constraints in Oracle with Examples

Disable CHECK Constraint in Oracle:

Let us DISABLE the Check constraint by executing the following query.

ALTER TABLE TEST15 DISABLE CONSTRAINT CHK_SALARY;

Once you execute the above query, again, try to execute the same INSERT statement as follows.

INSERT INTO TEST15 VALUES ('Sambit', 3000);

Now, when you execute the above query, it is executed successfully. This is because now it is not validating the Salary column values.

Enable CHECK Constraint in Oracle:

Let us ENABLE the Check constraint by executing the following query.

ALTER TABLE TEST15 ENABLE CONSTRAINT CHK_SALARY;

When you execute the above query, you will get the following error i.e. ORA-02293: cannot validate (SYSTEM.CHK_SALARY) – check constraint violated.

How to Disable and Enable Constraints in Oracle

To overcome the above problem then we need to use the “NOVALIDATE” keyword at the time of enabling constraint. Once we use the “NOVALIDATE” keyword then the constraint is enabled with “NOVALIDATE” and the oracle server will not check existing data in the table but check new data while inserting time. So, execute the following query.

ALTER TABLE TEST15 ENABLE NOVALIDATE CONSTRAINT CHK_SALARY;

Once you execute the above query, now try to execute the following INSERT statement.

INSERT INTO TEST15 VALUES ('Kumar', 2000);

When you try to execute the above query, you will get an error as shown in the below image. This is because we are inserting 2000 as the value to the Salary column and the check constraint now verifies the value and the validation Failed and hence it is throwing an error.

How to Disable and Enable Constraints

Note: In the same way, you can Enable and Disable all other types of Constraints in Oracle.

In the next article, I am going to discuss DEFAULT CONSTRAINT in Oracle with Examples. Here, in this article, I try to explain How to Disable and Enable Constraints in Oracle with Examples and I hope you enjoy this How to Disable and Enable Constraints in Oracle with Examples article

Leave a Reply

Your email address will not be published.