Advanced Constraint Options

Constraint are objects we all know, we use them a lot when creating tables and they allow us to:

1. Keep data integrity at the table level, like disallow null values using the “not null” constraint, enforcing unique values using “unique” constraint or allowing only values that meet conditions using the “check” constraint.
2. Keep data integrity between tables using the “foreign key” constraint.
3. Provide information to the optimizer for query optimization.

In addition to the constraint type and basic characteristics, there are some more advanced options we can configure and can help us in specific cases. In this post I’ll explain some of these advanced options.

Deferrable Constraints

When we have a constraint on a column, and we insert or update a value that violates this constraint, the command itself will fail immediately with an error message. This is the default behavior and usually this is fine in most cases. For example, an insert that will attempt to add a duplicate value to a column with unique constraint will fail with ORA-00001.
There are, however, cases where we have to violate constraints during our DML statements, knowing we are going to fix these violations later on. Example can be a table with foreign key which we have to delete (for some reason) the parent row before the child, something that is illegal from the constraint point of view. We plan, of course, to delete the child row as well, but the delete of the parent will fail immediately and we won’t be able to do this.
In order to allow such flows, we can configure the constraint as “deferred”. Once we do that, we can violate the deferred constraint during our transaction. The constraint enforcement will take place only when we commit the transaction.
In order to configure deferred constraint it needs to be created with the keyword “deferrable”. This option is configured only when the constraint is created and cannot be changed on an existing one.
Now, we can change the constraint to use the default behavior (called immediate) or the deferred behavior. We can do this by changing the constraint itself, which will take place for all transactions, or use the “set constraint” command at the beginning of a transaction to change the behavior for the transaction locally.
It’s important to understand that there are implications of using deferrable constraints:

1. During a transaction a constraint can be violated, so Oracle might change its behavior when using deferrable constrains. For example, when configuring unique constraint, Oracle usually creates a unique index automatically. With deferrable unique constraint, there might be duplicate rows during a transaction, therefore, Oracle can’t create a unique index. In this case it will create a regular index instead.
2. For the same reason, the optimizer cannot assume uniqueness, so it cannot use “index unique scan” or make some assumptions (e.g. assume that every child record in foreign key has a matching parent record).
3. When we violate the constraint we will get the error message only when committing. In this case it is much more difficult to understand which statement violated the constraint.

Enable and Validate

When we create a constraint on an existing table, we need to consider existing data as well as new data. In order to specify how the constraint will be enforced we will use the enable/disable and validate/novalidate:

• Enable validate – This is the default. Oracle makes sure that all existing data does not violate the constraint and the constraint will be enforced on any new data inserted into the table.
• Enable novalidate – Oracle assumes that the existing data does not violate the constraint and will not check it. Oracle will enforce the constraint on new data inserted into the table.
• Disable validate – This configuration is useful for DWH databases. The constraint is not enabled, but because it is configured as valid, Oracle assumes that the data does not violate the constraint. With this configuration, DML operations are not allowed on the table, except SQL*Loader and exchange partitions.
• Disable novalidate – The constraint exists but is not active, not enforced and Oracle doesn’t use it at all.

Rely and Norely

Once we have a constraint on a table, the optimizer can use the constraint definition in order to perform SQL optimization. A simple example can be a check constraint that allows only “true” or “false” values in a specific column. A query that returns rows with the value “yes” doesn’t have to scan the table at all. As the constraint is valid and does not allow “yes” as a value, the optimizer knows that there won’t be any rows returned.
If we have a novalidate constraint on the column, Oracle does not enforce it, so the Optimizer cannot use this information to optimize the query above. If we are sure that the data is correct, don’t want Oracle to enforce the constraint, but would like the Optimizer to use this information, we can configure the constraint as “rely”.
Note that if we configure the constraint as “rely”, the Optimizer will use it but Oracle will not enforce it. If we have non-valid data in the table, we might get wrong results as we ask the optimizer to assume something that is incorrect.
The default for novalidated constraint is norely.

Exceptions

When we create a constraint on an existing table, there might be data that violates the constraint. In this scenario, Oracle will not create the constraint, but return an error message. The problem sometimes is to find the violating rows. To solve this, we can use the “exceptions into” clause when creating the constraint (after we created the required table using the ORACLE_HOME/rdbms/admin/utlexcpt.sql script). The constraint creation will still fail, but info about the violating rows will be inserted into the exceptions table. Now we can find the rows and fix the problem before trying to create the constraint again.
Hope you find this information useful.
Liron