Skip to content

Personal tools
You are here: Home » Oracle » Oracle Articles Archive » Back to Basics: Constraints - Part 1
Seeking new owner for this high-traffic site.
Tap into the potential of this DBA community to expand your business! Interested? Contact us today.
Who Are You?
I am a:
Mainframe True Believer
Distributed Fast-tracker

[ Results | Polls ]
Votes : 3548

Back to Basics: Constraints - Part 1

by Jonathan Lewis

Part 1  |  Part 2  |  Part 3

It is important to revisit the basics regularly or you find that your favourite mantras, beliefs, and techniques are so far out of date that they are not only inadequate, they may even be bad practice.

Constraints have come a long way in Oracle since their introduction some 15 years ago, but many people still have only a faint idea of how important they are, and how they can be used most effectively in Oracle systems.

Any code fragments in this document were run against Oracle

What is a Constraint?

In the Oracle implementation, the concept of a constraint can be explained in just two sentences:

      1. A constraint is a statement that describes some feature of your (committed) data.
      2. When a constraint is validated and enabled, the database will not allow the statement to be false.

I have chosen the wording very carefully to allow these two simple sentences to convey the full power and importance of constraints.

Sentence 1 tells us that a constraint is completely static — it is a single point definition that tells us something about the state of (the data in) the database.

Sentence 2 tells us that the database protects itself. We can write code to modify the data, but if we try to leave the data in an illegal state, the database will reject the attempt.

If we were able to work out all our business rules, and specify them in the database as a set of constraints (and if we had an RDBMS that could implement all such constraints), then we would never have to worry about badly formed, or inconsistent, data appearing in our databases ever again.

Unfortunately, Oracle does not allow you the full spectrum of constraints implied by the SQL standard; in fact, it doesn’t even allow for the possibility of assertions (a related mechanism that you might think of as vaguely similar to a “standalone” constraint, or a “commit-time trigger”).

However, there is still a lot of value in understanding constraints, and keeping yourself up to date with the continuing range of enhancements that Oracle offers in this area.

Quick Summary

Oracle offers the following basic types of constraints:

      • Check constraints
      • Primary key constraints
      • Unique key constraints
      • Referential integrity constraints
      • REF scope constraints (for OO)

Not only do you have constraints on tables; you can create constraints on views:

      • View check option constraints
      • View read-only constraints
      • View primary key constraints
      • View unique key constraints
      • View RI constraints

The first two types of view-based constraint have been around for several years; the last three have appeared more recently and exist to help the optimiser do clever tricks, particularly in the area of query rewrite.

In fact, the newer constraints on views have only been possible since the introduction of various other features of constraints. You have far more control over the state of a constraint than you may realise. A constraint can be:

      • Enabled / disabled
      • Validated / novalidated
      • Deferrable / non-deferrable
      • Deferred / immediate ***
      • Rely / norely

*** A constraint can only be defered if it was originally defined as deferrable.

The newer view-based constraints have to be declared as disabled, novalidated, and (if you want them to have any effect) rely.

Constraint States

A constraint may be defined as deferrable, which means you can switch it between deferred and immediate. If a constraint is in the deferred state, you can modify data in a way that fails the constraint test. But a constraint can only be deferred for the duration of a transaction. As soon as the transaction commits, the constraint becomes immediate. (You also have the option of explicitly setting the constraint back to immediate before the end of the transaction). At this point, your modifications will be tested against the constraint, and you will not be allowed to commit a transaction that leaves the data in an illegal state.

For example: I have two tables, parent and child, with the obvious referential integrity constraint. The RI constraint is called chi_fk_par, and is deferrable. I have a parent row with two related child rows, and I want to change the parent id. The following is an extract from a possible SQL*Plus dialogue:

set constraint chi_fk_par deferred;

Constraint set.

update parent
set id = 2 where id = 1;

1 row updated.

-- so two child rows just became
-- orphans – test the constraint

set constraint chi_fk_par immediate;
SET constraint chi_fk_par immediate * ERROR at line 1:
ORA-02291: integrity constraint (TEST_USER.CHI_FK_PAR) violated –
parent key not found
update child set id_p = 2 where id_p = 1; 2 rows updated.

-- the orphans are back to their
-- parent – test the constraint

set constraint chi_fk_par immediate;

Constraint set.


Commit complete.

Note: If you want a primary key, or unique key constraint on a table to be deferrable, it has to be supported by a non-unique index. This does have a small side effect on the index size and the CBO cost calculations, so don't do it unless you have a proper reason to do so.

A constraint may be enabled or disabled. If it is disabled, then it is simply ignored as you change the data. Its existence merely allows you to know that, in principle, there was some restriction on the data — but you aren't trying to enforce it at the database level. However, if you also declare the constraint with the rely option, then the optimiser has some code paths that will behave as if the constraint were enabled, without going to the expensive of checking. (This option is particularly relevant in data warehouses, where you may not want to use resources in the database on validating constraints because you “know” the data load is clean.

A constraint that is enabled need not be validated. For example, you might declare a constraint that restricts a column to be within a certain range of values.

Consider the following extracts from an SQL*Plus dialogue as we try adding such a constraint to an existing set of data:

-- Try adding a constraint

alter table t1
add constraint t1_ck_n1
check(n1 between 0 and 1000000)

ERROR at line 2:
ORA-02293: cannot validate (TEST_USER.T1_CK_N1) –
check constraint violated
-- Add it enabled, but novalidate alter table t1 add constraint t1_ck_n1 check(n1 < 1000000) enable novalidate;

Table altered.

-- Some existing data is
-- still invalid

select * from t1
where n1 = 1000001;

1 row selected. -- But I can't insert new invalid -- data (or do an update to make -- that column invalid) insert into t1 values (1000002); ERROR at line 1:
ORA-02290: check constraint
(TEST_USER.T1_CK_N1) violated
-- I can't validate the existing -- data yet – too much garbage. alter table t1 modify constraint t1_ck_n1 validate ; ERROR at line 1:
ORA-02293: cannot validate (TEST_USER.T1_CK_N1) –
check constraint violated
-- I have to remove or correct -- the invalid data first delete from t1 where n1 > 1000000; 123600 rows deleted.

alter table t1
modify constraint
t1_ck_n1 validate ; Table altered.

Perhaps the most important point to note in this cycle of events is something that simply doesn't show up on the page.

When I attempted to use the basic command, with no special options, to add the constraint to the table, Oracle locked the table and scanned all the data to validate the constraint before enabling it (and then failed after about 15 seconds because it was a fairly large table, and there was a lot of data to read before the first error was found). This behaviour could cause problems on a busy system.

When I added the constraint to the table in the enabled but novalidate state, Oracle only had to lock the table very briefly as it added the constraint. Oracle did not attempt to validate the constraint and therefore did not have to lock the table for very long. (This becomes particularly relevant if you want to do things that could take a long time, such as adding a referential integrity constraint)

Note: If the table had been subject to an uncommitted change from another session the attempt to add the constraint (with or without validation) would have resulted in error:

ORA-00054: resource busy and
acquire with NOWAIT specified

When I changed the constraint from novalidate to validate, Oracle knew that any data changes or incoming data would be checked at data-entry time because the constraint was already enabled. Since the only thing needing validation was the existing data, there was no need to lock the table whilst validating the constraint.


There is a lot more to constraints than you may think. If you aren't making effective use constraints, then you're not really making proper use of a relational database system.

If you are aware of all the subtleties of constraint states, and how they can be manipulated then you may find options for reducing locking time or validation costs in data loading or similar batch tasks.


This article is based on material from the book Practical Oracle 8i, and also includes material from the seminar Optimising Oracle ™.


Jonathan Lewis is a freelance consultant with more than 18 years' experience of Oracle. He specialises in physical database design and the strategic use of the Oracle database engine. He is one of the best-known speakers on the UK Oracle circuit, and is the author of Practical Oracle 8i - Designing Efficient Databases published by Addison-Wesley. He can be contacted on +44 (0)7973-188785, or contacted by e-mail at: Further details of his published papers, presentations, seminars and tutorials can be found at, which also hosts The Co-operative Oracle Users’ FAQ for the Oracle-related Usenet newsgroups.

Jonathan Lewis
Last modified 2006-01-05 09:37 AM
Transaction Management
Reduce downtime and increase repeat sales by improving end-user experience.
Free White Paper
Database Recovery
Feeling the increased demands on data protection and storage requirements?
Download Free Report!

Powered by Plone