Part 1  |  Part 2  |  Part 3

In the first article on constraints, I restricted myself to details of the possible constraint states, pointing out that they have come a long way from the simple “on” and “off” options that used to exist in earlier versions of Oracle.

In this article, I move on to make a few points about the different types of constraints that exist — picking up, in particular, the added value you can get by using constraints properly.

Any code fragments in this document were run against Oracle 9.2.0.4.

Overview

Oracle has allowed active check constraints, primary key (PK), unique (UK), and referential integrity (RI) constraints on tables for a long time, and has recently added PK, UK, and RI constraints to views as well.

For users of views, the read only constraint and the check option constraint have also been around for a very long time.

For enthusiasts of the Object Option, there are also various constraints relating to REF types that can be used to restrict the scope of REFs that have been stored in the database.

Finally, for users of the partitioning option, you could consider partition boundaries, or partition value lists, as a further type of constraint on the database.

In this article I am going to address only the oldest, simplest constraint type — the check constraint.

Check Constraints

Check constraints can be declared in-line (i.e., as part of a column definition) or out-of-line (i.e., at the table level). In either case, they are used to check the values of columns on a row-by-row basis. The tests you can do on a row are quite limited, may use only a subset of the available built-in SQL functions, and can only involve comparisons between columns in a row if the constraint was declared out-of-line.

A row will not pass the check constraint if the test built into the constraint returns the value FALSE. For example the following is a valid out-of-line check constraint:

alter table t1 
	add constraint t1_ck_unusual
		check(length(vc) < n1)
;

More commonplace constraints might appear at the table definition stage:

create table t1 (
	v1 varchar2(30) 
		constraint t1_ck_v1 
		check (v1 = upper(v1)), 
	v2 varchar2(20)
		constraint t1_ck_v2 
		check (v2 in ('TX','MA')) 
)
;

You will note that I have named the constraints in both my examples. If you don’t name constraints, they are given names like “SYS_Cnnnnnn” where the nnnnnn represents a 6-digit number. If you find an unnamed constraint, you have the option in 9i for renaming it with commands like:

alter table t1 rename constraint
	sys_C012345 to t1_ck_v1;

Unnamed constraints can cause silly problems if you are in the habit of exporting and re-importing tables. Unless you are careful, you could end up with multiple copies of the same check constraint, each with a different system-generated name.

A quick check for this problem would be a visual scan of the results from a query like:

select 
		table_name, 
		constraint_type,
		search_condition 
from 
		user_constraints 
order by 
		table_name,
		constraint_type
;

A detail that is often overlooked with check constraints is in a little bit of fussy wording — the constraint fails if the test returns FALSE. Don’t forget that Oracle uses three-valued logic; the options are true, null, false. If a check constraint returns null, then the test has not failed, and a row will be accepted.

For example, at first sight, you may think that my check constraint on column v2 above ensures that the column will only ever hold the values “TX” or “MA” but try this insert – it will succeed:

insert into t1 values (null,null):

Be careful of check constraints that can evaluate to null.

Optimisation

Apart from their importance in ensuring data correctness, constraints, especially check constraints, can help the optimiser to find better execution paths.

It is probably common knowledge by now that the presence of a not null constraint can give the optimiser more options using indexes and unnesting subqueries. It is probably less well-known that a well-chosen check constraint could make a function-based index redundant. Consider the following code fragment:

create table t1 as
select	
		upper(object_name)	v1, 
		rpad('x',100)	padding
from	all_objects
where	rownum <= 5000
;

alter table t1 add constraint
t1_ck_v1 check (v1=upper(v1));

alter table t1 modify v1 not null;

create index t1_i1 on t1(v1);

select *
from t1
where
upper(v1) = 'ALL_OBJECTS'
;

How could Oracle use the index on column v1 with a high-precision range scan to satisfy this query?

If you remember Oracle 7 and partition views, you will remember that you could use constraints to describe the partitioning rules to Oracle and the optimiser would add the text of the constraints to your query to decide which partitions could be eliminated.

Something similar is happening here. Oracle appends to the basic query the constraint:

	v1 = upper(v1)

Combining this with the predicate:

	upper(v1) = 'ALL_OBJECTS'

Oracle infers:

	v1 = 'ALL_OBJECTS'

So Oracle can use the index on v1.

A couple of points to note:

• the not null constraint (or a v1 is not null predicate) is needed before this works. This seems to be redundant
• The feature does not seem to work in 8.1, even when the parameter partition_view_enabled is set to true, and even though there is an event (10195) in the oraus.msg file that apparently exists to turn the feature off.

Not Null Constraints

There is a special case of the check constraint that was implemented and active in Oracle databases years before any other code for constraint handling was in place. This was, of course, the not null constraint.

Historically the condition not null was represented by a flag on the column definition in the data dictionary; but with the increasing sophistication of constraints and the ways in which they can be used, this is no longer always the case. Consider the traditional table-creation statement:

create table t1 (
	n1 number(6) not null
);

desc t1

Name              Null?    Type
----------------- -------- -------
N1                NOT NULL NUMBER

In more recent versions of Oracle, a not null definition introduces a proper check constraint as well.

select	constraint_name, 
		search_condition
from	user_constraints
where	table_name = 'T1'
;

CONSTRAINT_NAME   SEARCH_CONDITION
----------------- ----------------
SYS_C008519       "N1" IS NOT NULL

However, things can get confusing. Consider the following:

create table t1(n1 number(6));

alter table t1 add constraint t1_pk primary key (n1) deferrable;

desc t1

Name              Null?    Type
----------------- -------- -------
N1                         NUMBER

select	constraint_name, 
		search_condition
from	user_constraints
where	table_name = 'T1'
;

CONSTRAINT_NAME   SEARCH_CONDITION
----------------- ----------------
T1_PK

insert into t1 values(null)

ERROR at line 1:
ORA-01400: cannot insert NULL into
("TEST_USER"."T1"."N1")

We don’t see a not null restriction on the table, and there is no sign of a not null check constraint — yet we get an error message that tells us we have a column that may not be null.

The answer, of course, lies with the primary key constraint. A column that belongs to a primary key is implicitly not null. In fact, if we had declared the primary key to be not not deferrable (the default), we would have seen our describe command reporting the column to be not null.

Be careful when you start to take advantage of deferrable constraints; the scripts you used to use for checking constraints, and nullability in particular, may no longer work the way you expect.

In fact, there is a small bug with deferrable primary key constraints: when the constraint is set immediate in the following code, it should cause an error:

ORA-01400: cannot insert NULL ...

But this doesn’t happen until (a) you set ALL constraints immediate, or (b) you commit:

drop table t1;
create table t1(n1 number);

alter table t1 
add constraint t1_pk 
primary key (n1) deferrable;

set constraint t1_pk deferred;
insert into t1 values(null);

You can get the same confusion in much simpler cases:

set constraint t1_pk immediate;
-- no error yet

set constraints all immediate;
-- raises the correct error

commit;
-- raises the correct error

Again, when you describe the table, you won’t see not null in the description, and you will have to go to view user_constraints to discover a check constraint.

And look what happens when you try to insert a null into this column:

create table t1 (
	n1 number not null deferrable
);

You don’t get the normal ORA-01440 error (cannot insert null) — so if you are in the habit of writing code to trap and resolve specific errors, you may find that you have a crop of untrapped errors when you switch to deferrable constraints.

Conclusion

There is a lot more to constraints than you may think. This is just one area of Oracle in which it is important to stay up to date with the possibilities, rather than assuming that the things you learned five years ago are still valid and all you need to know.

Even something as simple as a check constraint has features that could make a big difference to correctness, performance, data-loading and housekeeping.

Acknowledgments

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

See also “SQLTuning Improvements in 9.2” by Vadim Tropashko, which describes Oracle’s clever use of constraints and transitive closure to discover indexed access paths.

--

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: jonathan@jlcomp.demon.co.uk. Further details of his published papers, presentations, seminars and tutorials can be found at http://www.jlcomp.demon.co.uk, which also hosts The Co-operative Oracle Users’ FAQ for the Oracle-related Usenet newsgroups.