3.7 Modifying Constraints

You can disable a constraint with the syntax:

 ALTER TABLE  table_name  DISABLE CONSTRAINT  constraint_name  ; 

This leaves the constraint defined in the data dictionary. It is just not being enforced. You might do this on a development database where you need to load data that you expect will not conform to the business rules. Once the data is loaded you view it with SQL*Plus. If the data looks good you can try to enable the constraint with:

 ALTER TABLE  table_name  ENABLE CONSTRAINT  constraint_name  ; 

If the data is bad, the ENABLE command will fail. The Oracle error message will indicate that the constraint cannot be enforced because the data does not comply with the rule. There are three options when the data is bad and you cannot enable the constraint:

• Delete the data you inserted.

• Enable the constraint using an exceptions table ”this is discussed in Section 3.9, "Data Loads."

• Enable the constraint using the NOVALIDATE option. This syntax is:

   ALTER TABLE  table_name  ENABLE CONSTRAINT  constraint_name  NOVALIDATE; 

The NOVALIDATE option enables the constraint but for future transactions. The data present in the table does not have to comply with the constraint. This can have some serious consequences. For example, many components of an application rely on business rules. A text box on an HTML form may be populated with a query that uses a primary key in the WHERE clause ”this is a situation where the program assumes that one row is returned. This code will crash if it runs and multiple rows are returned.

The NOVALIDATE option poses no threat if the nature of the task is to study and analyze historical data. You can leave the old, noncompliant data in the table, enable constraints, and proceed with loading some new data that you wish to conform to your business rules. But the NOVALIDATE option on an existing production system can break many applications.

When you DISABLE a primary key or unique constraint, the index is dropped. So, if the table is large, you may see some time delay when you enable the constraint. This is the time it would take to rebuild the index. The same holds if you DROP the constraint; the index is dropped.

You use a CASCADE option when you DISABLE or DROP a primary key of unique constraint that is referenced by a foreign key. This syntax is:

 ALTER TABLE  table_name  DISABLE CONSTRAINT  constraint_name  CASCADE; ALTER TABLE  table_name  DROP CONSTRAINT  constraint_name  CASCADE; 

You cannot accidentally corrupt the enforcement of referential integrity. If you don't realize there is a foreign key constraint and skip the cascade option, the ALTER TABLE fails with the Oracle error:

 ORA-02272: this unique/primary key is referenced by some foreign key