Skip to content

Personal tools
You are here: Home » Oracle » Oracle Articles Archive » Back to Basics: Constraints - Part 3
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 3

by Jonathan Lewis

Part 1  |  Part 2  |  Part 3

In my second article on constraints, I managed to cover the more important features of check constraints. In this article, I move on to the other constraints that are commonly applied to tables — primary keys, unique keys, and foreign keys.

Any code fragments in this document were run against Oracle


The constraints that I will be discussing in this article are generally referred to as integrity constraints. You could say that they exist to ensure that the structure of data holds together properly. In particular, foreign key constraints are often called referential integrity constraints, and I will probably slip back and forth between the two different terms a little casually.

Similarly, we often talk about parent/child tables, meaning a pair of tables for which there is a referential integrity constraint in place — the parent has a primary (or unique) key and the child has a foreign key that refers to the parent. Again, I will probably fail to stick to a single choice of expression all the way through the article.

PK / UK constraints

I have put primary and unique key constraints under a single heading because they are virtually identical. In fact, they differ in just one detail: the columns of a primary key are implicitly not null columns. (And I try to insist that any unique keys should be made explicitly not null, anyway)

Rather than talk through the options for defining these constraints, I will list a (slightly unusual) piece of code that creates a primary key constraint:

drop table t1; 

create table t1 (
id1 number,
id2 number,
n1 number,
v1 varchar2(20)

insert into t1 values (1,1,1,'x');
insert into t1 values (1,1,1,'x');
insert into t1 values

create index t1_i1 on
t1(id2, id1, n1);

create index t1_i2 on
t1(id1, id2);

alter table t1
add constraint t1_pk
primary key(id1,id2)
using index t1_i2
enable novalidate


I like to have scripts to generate objects, and I like to keep the SQL for creating indexes and constraints separate from the SQL for creating the tables, as this makes it a little cleaner and safer to drop and recreate indexes and constraints. This is why I have not included the primary key constraint as part of the table definition. This also explains why I have created an index for the constraint before creating the constraint itself.

Note, however, that I have specified two non-unique indexes that could be used to enforce the primary key, and that one of them has an extra column tagged on the end. I’ve then specified which index should be used to enforce the constraint — and included a novalidate option as well.

The code is not particularly sensible — it’s just demonstrating options. But it does allow me to mention several points.

First, of course, you will note that I have two identical rows, and a completely null row in the table — but I have created a primary key constraint (and yes, Oracle created it without complaint). This is the point of the novalidate option. Because of the enable option, new data (or data that is modified to change the primary key columns) will be checked for uniqueness, but Oracle does not check existing data against the constraint. This means the constraint can be added quickly (without locking the users out, possibly for a long time). Existing data will not be checked until you try to execute:

alter table t1 enable validate
constraint t1_pk
ERROR at line 1:
ORA-02437: cannot validate
primary key violated

There is some benefit to being able to create and enable constraints in this way — you could, for example, validate several constraints concurrently after a data load. However, there is still a moment when the table needs to be locked briefly as the constraint it created, so there is still a chance of some queueing occurring on an active system.

The second point behind my odd example is a nasty little trap. If you export, drop, and re-import this table definition, you will probably find (as I did) that after the import, the constraint is enforced by the “wrong” index; i.e., t1_i1. This is because the SQL generated for the import cannot specify the index name in its using clause, and I think Oracle chooses the first index that matches the logical requirement, based on alphabetic order of name.

As we shall see in the next section, this obscure little detail can cause other problems. (Historically, things were much worse: There were cases in which an import command could find an index that could enforce a primary or unique key, and didn’t even bother to create the index that had originally existed for the constraint.)

The final point of the example is that there is one more option for the state of the constraint, the rely option. In my example, my primary key was a fake — but only because I had some bad data in place. I can tell the optimiser to trust my definition (as far as its costing arithmetic is concerned) if I claim that the constraint is reliable. (There is one little oddity with the rely option: Although the syntax diagrams in the manual indicate that the word order is irrelevant, it has to go before the enable / validate options).

Referential Integrity Constraints

In principle a referential integrity constraint allows a row to exist in a child table only if the foreign key columns in that row match the values of the primary key columns in one of the rows in the related parent table. (In fact an RI constraint can be defined against a unique constraint, not just a primary key constraint — this is an option that is often overlooked.)

However, the SQL standard allows three different matching strategies for foreign keys, and Oracle uses the “partial match” strategy. This means that if any column in the foreign key is null, then the key automatically matches the parent table, even if there is clearly no reasonable match that the human eye can see. As we can see in this (minimalist) code fragment:

create table parent(
id1 number,
id2 number,
primary key (id1, id2)

create table child (
ref1 number,
ref2 number,
foreign key (ref1, ref2)
references parent
on delete set null

insert into child values(2,2);
insert into child values(2,null);

The second child insert does not raise an error.

For this reason, I like to insist that foreign key columns should be declared as non-null — and tend to suspect that there is a design error somewhere if this cannot be done.

In order to maintain data integrity on foreign keys, you need to choose an action that takes place automatically at the child table when a parent row is deleted — the possibilities are:

      • No action — which means if there are any child rows belonging to a parent, the delete will not be allowed.
      • Set Null — as indicated in the example above; but if you have declared the child columns as not null, the delete from parent will raise an error as it tries to set the columns to null on the corresponding child rows.
      • Cascade — when deleting a parent, first delete its children.

There is no equivalent choice for updating the key columns at the parent — you have to play around with deferrable constraints to change parent and child rows separately.

There are a couple of implementation traps with foreign key constraints. If you are going to delete parent rows, or update key values of parent rows, then you will probably need to create an index on the child table that starts with the foreign key columns. (Note: It is sufficient for the index to start with the columns, not be an exact match for the foreign key, and those columns don’t even need to be in the order that they appear in the foreign key declaration.)

If you don’t have such an index, then Oracle will request a mode 4 (share) or mode 5 (share row exclusive) lock on the child table, and wait for all other updates on that table to commit before attempting to modify the parent. Even allowing for the Oracle 9 “fix” (which releases the lock as soon as it has been acquired), this may cause significant queueing problems. (Remember that the terminology used internally for locks is enqueues — that’s because they are expected to cause queues.)

I mentioned in the section on primary and unique keys that Oracle’s ability to pick an enforcing index on import can lead to a nasty surprise.

In my example, I had a primary key on columns (id1, id2), but Oracle picked the index on columns  (id2, id1, n1) to enforce it. If I update column n1 when this index is the one being used to enforce the constraint, Oracle behaves as if I have updated the primary key values — even though I haven’t. So if you don’t have indexes for some foreign keys because you know that you never update the corresponding primary keys and therefore don’t need them, be warned — you could find your system locking and deadlocking after an export/import cycle.

Of course, another reason why you might need pointless foreign key indexes when you don’t update the primary keys is because your application generator might be updating the primary keys behind your back. Many screen generators take the option to “update every column known to the screen,” rather than “update the columns that have changed.” A “no-change” update of a primary key still counts as an update.


When I first started to write about constraints, I planned to produce a single article that hit the highlights. After completing three articles, I still feel that I have barely managed to cover the more important points; and haven’t even managed to mention the dangers of “checking the constraints in the application” and failing to use the database properly to protect the integrity of your data.

Keep a careful eye on where Oracle takes its implementation of constraints. Every enhancement to the internal constraint handling code makes your applications more robust, and more efficient. You need to look at every enhancement as soon as it appears.


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.

Contributors : 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