OracleBlog

I was ready to explain to you why you should take advantage of Oracle's ability to manage the integrity of your data rather than rely on your applications, but I found a much better explanation in the Oracle Data Warehousing Guide. Read the section "Why Integrity Constraints are Useful in a Data Warehouse" in Chapter 7 on "Integrity Constraints."

So instead, let me give you a just a really quick primer from my own experience, and a couple of treats.

There are many types of constraints, including primary key, unique, referential (foreign key) and check constraints. I'll talk about check constraints.

There are basically three ways to set up your table constraint. Check a reference (like Dan Morgan's http://www.psoug.org/reference/constraints.html) for more detail, but I will review them here.

1. On the Same Line

CREATE TABLE ConstraintTable (
MyNumber NUMBER(1) CHECK (MyNumber < 5)
);

Using this method (only), you can't reference other columns in the table in your check constraint. Try it:

CREATE TABLE ConstraintTable (
MyNumber1 NUMBER(1),
MyNumber2 NUMBER(1) CHECK (MyNumber2 > MyNumber1)
);

ORA-02438: Column check constraint cannot reference other columns

It is also not obvious what name the constraint takes so it's more difficult to alter it later. But here is how:

SQL> SELECT constraint_name
2 FROM user_constraints
3 WHERE table_name = 'CONSTRAINTTABLE';

CONSTRAINT_NAME
------------------------------
SYS_C007536

2. During table creation

Example:

CREATE TABLE ConstraintTable (
MyNumber NUMBER(1)
CONSTRAINT c_my_number CHECK (MyNumber < 5)
);

Doing it this way allows you to reference other columns in the table:

SQL> CREATE TABLE ConstraintTable (
2 MyNumber1 NUMBER(1),
3 MyNumber2 NUMBER(1),
4 CONSTRAINT c_my_number CHECK (MyNumber2 > MyNumber1)
5 );

3. Alter table

You can create your table as normal, and then add your constraints separately. I don't think there is any actual difference to Oracle between method #2 and #3.

CREATE TABLE ConstraintTable (MyNumber Number(1));

ALTER TABLE ConstraintTable ADD CONSTRAINT c_my_number check (MyNumber < 5);

There is actually a 4th way, kind of. See, CHECK constraints can not include sub-queries, and you can't reference other tables in them. You also can't use package-defined constants or variables. All you can basically do is simple things, like <>=, and [not] between/in/like/equals

I get around all of this by using triggers. I add a simple constraint to the table (possibly a "NOT NULL", if applicable) and then write a trigger which will do my check - referencing other tables, writing subqueries and using package-defined constants as I please - and then deliberately set off the simple constraint.

Of course, this may look silly to a user, who gets an error on inserting a row with a NULL value when it clearly isn't NULL. So I usually write something to a log, or name my simple constraint, and I certainly document the source code. But that's a topic for another day.

Here is an article where I describe the concept in more detail, picking especially on using variables in constraints. But apply the same logic for complex integrity constraints when you want to reference other tables.

http://thinkoracle.blogspot.com/2005/06/variable-constraints.html

Ok, let me wrap it up by saying that one of the other advantages of constraints is that you can disable them when you need to:

SQL> INSERT INTO ConstraintTable (MyNumber) VALUES (6);

ORA-02290: check constraint (SYS.SYS_C007536) violated

(By the way, that's the other way to find out the name of your constraint: violate it!)

SQL> ALTER TABLE ConstraintTable DISABLE CONSTRAINT SYS_C007536;

SQL> INSERT INTO ConstraintTable (MyNumber) VALUES (6);

And you're ok! Of course, you better make sure the data is ok by the time you turn it back on, or you'll get this error:

SQL> ALTER TABLE ConstraintTable ENABLE CONSTRAINT SYS_C007536;

ORA-02293: cannot validate (SYS.SYS_C007536) - check constraint violated

Which is a great segue into my closing, which includes two of my favourite discussions on disabling and deferring constraints. Check them out:

Jeff Hunter
http://marist89.blogspot.com/2005/06/deferrable-constraints_29.html

Doug Burns
http://doug.burns.tripod.com/oracle/index.blog?entry_id=1170846