SkillBuilders | Oracle 9i Administration

Instructor's Notes

(If applicable)

Lesson 15: Constraints
This lesson is dedicated to table constraints. After you learn what they are, we will discuss what I hope are the most common techniques and issues related to constraints, including:

Creating
Violating
Using the NOVALIDATE option to add a constraint without validating existing data
Disabling
Dropping and renaming

As usual, you will also learn about the data dictionary views related to this topic.

Other Text:
(Examples or comments displayed on slide, if any).

Instructor's Notes

(If applicable)

Constraints are business and integrity rules applied to data. A simple example is “the customer number must be unique”. There are six types of built-in constraints, five of which we will cover in this lesson:
1. Primary Key - Value in column(s) must be unique and not null. Oracle creates a new index or uses an existing index to support this constraint.
2. Unique - Value in column(s) must be unique. Oracle creates or uses an existing index to support this constraint. You’ll learn more about indexes in the next lesson.
3. Not Null - Column will not accept NULL; a value must be supplied on INSERT and UPDATE operations.
4. Check - Value in column must adhere to criteria specified, e.g. “BETWEEN ‘A’ AND ‘Z’ “. All common relational operators are available such as BETWEEN, IN, LIKE, =, >=, >, <, <=. Subqueries are not supported.
5. Foreign Key - Value in column must match a value in primary key of specified parent table.

Notes continue on the next page…

Other Text:
(Examples or comments displayed on slide, if any).

Can also enforce constraints with triggers
REF Constraint not covered in this course

Instructor's Notes

(If applicable)

REF Constraint – A REF column in a table references an object type. REF constraints further define the relationship between the REF column and the object. This is part of Oracle object support and is not part of this course. Refer to the Oracle9i SQL Reference for more information on this type of constraint.

Note that these constraints are built-in to the Oracle database. You can also write constraints with a trigger. However, built-in constraints usually out-perform triggers, so stick with the built-in constraints unless you have to code a trigger. Why would you “have to” code a trigger rather than enforce the rule with a simpler and more efficient built-in constraint? Well, constraints have limitations. An example: insure the ORDER_DATE column is within the last 2 days. Your first impulse may be to code:
SQL> create table orders
2 (id number primary key,
3 part_no number,
4 order_date date check (order_date >= sysdate-2) );
order_date date check (order_date >= sysdate-2) )
*
ERROR at line 4:
ORA-02436: date or system variable wrongly specified in CHECK constraint
However, SYSDATE is not supported in a CHECK constraint. A trigger will handle this:
1 create trigger t1
2 before insert on orders
3 for each row
4 begin
5 if :new.order_Date >= sysdate-2 then
6 null;
7 else
8 raise_application_error(-20001, 'Order date invalid');
9 end if;
10* end;
SQL> /
Trigger created.

Other Text:
(Examples or comments displayed on slide, if any).

Instructor's Notes

(If applicable)

One of the two methods of defining constraints is with “inline” constraint syntax. Inline constraints are part of a column definition – as shown above in the “CREATE TABLE T” example. Let’s discuss each part of the inline constraint syntax:

CONSTRAINT constraint-name – An optional clause (everything in brackets is optional) that provides for a user-defined constraint name. Oracle supplies a system-generated constraint name, e.g. SYS_Cnnnnnn if omitted. The "constraint-name" is any valid SQL name (1-30 characters). The constraint-name must be unique within the schema.
constraint-type – Specify one of the valid constraint types (e.g. PRIMARY KEY, REFERENCES, NOT NULL, UNIQUE, CHECK).
constraint-state – Optional clause that specifies the initial state of the constraint. Specify ENABLE | DISABLE and VALIDATE | NOVALIDATE. Further discussion of state is presented later in this lesson.
references-clause – Use to define a foreign key column. “[schema.]table” is the name of the parent table. [column] is optional; the primary key column of the parent table is used by default. See the section on Referential Integrity later in this lesson for more information.

Notes continue on the next page…

Other Text:
(Examples or comments displayed on slide, if any).

Inline constraint
SQL> create table t (c1 number primary key);

Table created.

Inline constraint SYNTAX
[CONSTRAINT constraint-name] constraint-type [constraint-state]

“constraint-types”
[NOT] NULL
UNIQUE
PRIMARY KEY
CHECK ( condition )
references-clause

“references-clause”
REFERENCES [schema.]table [column] [ON DELETE CASCADE | SET NULL]

Instructor's Notes

(If applicable)

ON DELETE CASCADE – Child rows in this table are deleted by Oracle if the parent row is deleted.
ON DELETE SET NULL – Dependent column in the child rows are set to NULL if the parent row is deleted.
If the ON DELETE clause is omitted, Oracle rejects attempts to delete a parent row that has children – an error is returned.

Refer to the Oracle9i SQL Reference for a complete description of the constraint syntax.
Supplemental Notes
- It is quite possible – and reasonably common – for a single column to have more than one constraint defined upon it. For example, the following example demonstrates creating a NOT NULL and a FOREIGN KEY constraint on column C1:
- SQL> create table test
- 2 (c1 number not null references t);
- Table created.

Other Text:
(Examples or comments displayed on slide, if any).

Instructor's Notes

(If applicable)

As stated earlier, the syntax provides two methods of defining constraints, inline and out-of-line. An out-of-line constraint is part of the table definition. Though all constraints can be coded out-of-line, it is required when the constraint refers to more than one column in the table, as the primary key constraint demonstrates in the example shown above.
The primary difference between inline and out-of-line constraints is simply syntactical; inline constraints do not contain the column name. They are – by definition – part of a column’s definition. The out-of-line constraint specifies the column name(s), for example: “PRIMARY KEY(C1, C2)”.
Here is a description of each constraint created in this slide:

C1_FK – Foreign key. Values in column C1 must exist in the primary key column of the parent table, “T”, or be NULL.
C1_VALUE – Check constraint. Values in column C1 must be between 0 and 10 or be NULL.
T2_PK – Primary key. This is a composite primary key (i.e. made up of multiple columns). The combination of values in columns C1 and C2 must be unique. The columns cannot be null. Oracle will create a unique index to help enforce this constraint.

Other Text:
(Examples or comments displayed on slide, if any).

SQL> create table t2
2 (c1 number,
3 constraint c1_fk foreign key(c1) references t,
4 constraint c1_value check (c1 between 0 and 10),
5 c2 number,
6 constraint t2_pk primary key (c1, c2) )
SQL> /

Table created.

3 out-of-line constraints defined
Index created to support constraint will have same name - “T2_PK”

Instructor's Notes

(If applicable)

All constraints are named – they are essentially an Oracle object. This allows us to recognize constraint violations (see next page) and refer to the constraint by name when disabling and enabling the constraint.
If the creator of the constraint does not supply a name, the database will create a name with the format “SYS_Cnnnnnn”, where “nnnnnn” is a sequence-generated sequential number.
Creating meaningful constraint names, e.g. “T2_PK”, helps in debugging. Turn to the next page for a discussion of this concept.

Other Text:
(Examples or comments displayed on slide, if any).

SQL> select table_name, constraint_name
2 from user_constraints
3 where table_name in ('T', 'T2');

TABLE_NAME CONSTRAINT_NAME
------------ ---------------------------
T SYS_C002815
T2 C1_VALUE
T2 T2_PK
T2 C1_FK

Constraints created in this lesson

Instructor's Notes

(If applicable)

Constraints “execute” (i.e. validate data) when an SQL statement is issued. (This is the default behavior. Constraint validation can be deferred to commit time; refer to “deferrable constraints” and the SET CONSTRAINTS ALL DEFERRED statement in the Oracle9i SQL Reference for more information.)
We can see (in the example shown above) that violating a rule defined by the constraint causes an error - the SQL statement is rolled back and an error message is returned.
If you want to be able to readily and easily identify which constraint (and column) is being violated, I recommend that you use user-defined names for all your constraints. (Remember that the constraint name must be unique within the schema.) This will aid in debugging because the constraint name is displayed in the error message when a constraint is violated. In the example above, the constraint name (“DAVE.C1_VALUE”) indicates some sort of value error related to column C1.
By querying USER_CONSTRAINTS as shown above, we can easily determine the exact cause of the failure – the value inserted into column C1 must be between 1 and 10.

Other Text:
(Examples or comments displayed on slide, if any).

SQL> insert into t2
2 (c1, c2) values(11,1);
insert into t2
*
ERROR at line 1:
ORA-02290: check constraint (DAVE.C1_VALUE) violated

SQL select constraint_name, search_condition
2 from user_constraints where constraint_name = 'C1_VALUE';

CONSTRAINT_NAME SEARCH_CONDITION
------------------------------ --------------------
C1_VALUE c1 between 0 and 10

Constraints “execute” when DML executed

Instructor's Notes

(If applicable)

Workshop – Constraints
1. Create a table called “T” that contains the following columns and constraints (see bottom of page). Provide user-defined, meaningful names for all constraints.
2. Test the constraints by inserting one or more rows into your table. Use values that will cause a failure of constraint validation. Successfully insert at least one row into the table.

Other Text:
(Examples or comments displayed on slide, if any).

Instructor's Notes

(If applicable)

Referential integrity is the notion of insuring that the values in one column (foreign key in the child table) exist in another column (primary key of the parent table). A practical example would be insuring that we do not add orders for non-existent customers.
Oracle enforces referential integrity with the REFERENCES constraint as shown in the second example above. Note that constraint can be defined with an out-of-line constraint, in which case the keyword “FOREIGN KEY” is used:
3 custno number,
4 constraint custno_fk foreign key(custno) references customers,
The REFERENCES constraint supports the following options:

ON DELETE CASCADE – Child rows in this table are deleted by Oracle if the parent row is deleted.
ON DELETE SET NULL – Dependent column in the child rows are set to NULL if the parent row is deleted.
If the ON DELETE clause is omitted, Oracle rejects attempts to delete a parent row that has children – an error is returned.

Notes continue on the next page…

Other Text:
(Examples or comments displayed on slide, if any).

Customers
1 Dave
2 Gary
3 Pat

Orders
1 1 Effective Oracle by Design
2 1 expert one-on-one Oracle
3 3 Linux Problem Solver

SQL> create table customers
2 (custno number primary key,
3 name varchar2(50) not null);

Table created.

SQL> create table orders
2 (orderno number primary key,
3 custno number references customers
4 on delete cascade,
5 book_title varchar2(50) not null);

Table created.
RI constraint prevents anomalies

SQL> create index orders_idx
2 on orders(custno);

Index created.
Important to create index on FK

Instructor's Notes

(If applicable)

Indexing foreign keys is important for performance and scalability:

The index on the foreign key column can significantly improve join performance. Remember that most joins use “PK_COLUMN = FK_COLUMN” in the WHERE clause.
DELETE CASCADE will, if the column is left unindexed, scan the child table for each row deleted in the parent.
The scope of lock can increase from row to table if rows in the parent table are deleted or updated and the foreign key column is left unindexed.

To find unindexed foreign keys in an existing database, use Tom Kyte’s script at http://osi.oracle.com /~tkyte/unindex/index.html.
Supplemental Notes
- A reasonable question is “what is the overhead of enforcing referential integrity?” The answer is “probably, on average, around 10%.” Tom Kyte has a concise test in Chapter 1 of his book Effective Oracle by Design. He states “the declarative referential integrity in the database added maybe 10% to 15% overhead. For that, you get the peace of mind…”

Other Text:
(Examples or comments displayed on slide, if any).

Instructor's Notes

(If applicable)

Constraints can be created when a table is created or added later with the ALTER TABLE statement (as demonstrated in this slide). Note that constraints added with the ALTER TABLE ADD CONSTRAINT statement are added with the out-of-line constraint syntax.
The ALTER TABLE statement will, by default, check all data in the table for compliance. This can take a long time for large tables. The ALTER TABLE statement will fail if there is invalid data, i.e. data in the column that does not adhere to the constraint. See the next page for a technique for determining which rows failed validation…

Other Text:
(Examples or comments displayed on slide, if any).

SQL> create table t3 (c1 number);

Table created.

SQL> alter table t3 add constraint t3_c1_pk
2 primary key(c1);

Table altered.
Constraint added with ALTER TABLE
Use out-of-line syntax

Instructor's Notes

(If applicable)

As this example demonstrates, you can use the optional EXCEPTIONS INTO clause to capture the ROWIDs of the invalid rows. (Review: A ROWID is a unique address of a row.) The ROWIDs are (be default) inserted into a table called “EXCEPTIONS”.
You can use the ROWIDS to fix the rows causing the error, perhaps deleting or updating the rows. For example, to delete the rows, you could:
delete from t3
where rowid in (select row_id from exceptions);
Then, you will be able to add the constraint without error.
Use the Oracle script named UTLEXCPT.SQL (found in $ORACLE_HOME/rdbms/admin directory) to create the EXCEPTIONS table. Here is a Windows example:
SQL> @%ORACLE_HOME%\rdbms\admin\utlexcpt.sql

Table created.
The UTLEXCPT.SQL script has also been supplied in your supplied scripts directory.

Other Text:
(Examples or comments displayed on slide, if any).

SQL> alter table t3 add constraint t3_c1_pk
2 primary key(c1) exceptions into EXCEPTIONS;
alter table t3 add constraint t3_c1_pk
*
ERROR at line 1:
ORA-02437: cannot validate (DAVE.T3_C1_PK) - primary key viola

SQL> select * from exceptions;

ROW_ID OWNER
------------------ -------
AAAHgrAAHAAAAj3AAA DAVE
AAAHgrAAHAAAAj3AAB DAVE

SQL> select * from t3 where rowid = 'AAAHgrAAHAAAAj3AAA';

C1
----------
1

Instructor's Notes

(If applicable)

Constraint Exceptions Workshop
1. Run the supplied script SETUP_CONSTRAINT_WORKSHOPS.SQL.
2. Create a table called “T” based on the supplied table “CONSTRAINT_ WORKSHOP_T”. Use a CREATE TABLE AS statement to build your new table. Copy all rows from the supplied table into your new table.
3. Try to add a primary key constraint to the “T” table on columns C1 and C2. Use the EXCEPTIONS INTO clause to capture ROWIDs of rows that fail validation.
4. Delete all rows that fail validation.
5. Add the primary key constraint.

Other Text:
(Examples or comments displayed on slide, if any).

Instructor's Notes

(If applicable)

A constraint that is added in VALIDATE state (the default) checks all existing data. This can take a long time if there's a lot of data and – will fail if there are violations. NOVALIDATE does not check existing data for violations. This could be useful, for example, with legacy data that may have some constraint violations or when you’re certain the data contains no anomalies.
With the constraint in NOVALIDATE state, only new DML operations must adhere to the constraint, as demonstrated here:
SQL> insert into t5 values(10);
insert into t5 values(10)
*
ERROR at line 1:
ORA-02290: check constraint (DAVE.T5_CHECK) violated

SQL> update t5 set c1=c1;
update t5 set c1=c1
*
ERROR at line 1:
ORA-02290: check constraint (DAVE.T5_CHECK) violated

SQL> update t5 set c1=1;
1 row updated.

Other Text:
(Examples or comments displayed on slide, if any).

SQL> create table t5
2 (c1 number);

Table created.

SQL> insert into t5 values(10);

1 row created.

SQL> alter table t5 add constraint
2 t5_check check(c1 between 1 and 5) novalidate;

Table altered.

Instructor's Notes

(If applicable)

Supplemental Notes
There are special considerations related to NOVALIDATE when it comes to PRIMARY KEY and UNIQUE constraints. These constraint types, by default, create a unique index that is used by Oracle to verify the constraint. So, you will encounter the following error because the unique index will not accept duplicates (please note that you will learn more about unique indexes in the next lesson of this course):
SQL> create table test
2 (c1 number );
Table created.

SQL> insert into test values(1);
1 row created.

SQL> insert into test values(1);
1 row created.

SQL> alter table test add constraint test_pk
2 primary key(c1) novalidate;
alter table test add constraint test_pk
*
ERROR at line 1:
ORA-02437: cannot validate (DAVE.TEST_PK) - primary key violated

As the following code demonstrates, this situation can be corrected by creating the constraint as “deferrable”, because deferrable primary key and unique constraints cause the creation of a non-unique index:
SQL> create table test
2 (c1 number constraint test_pk
3 primary key
4 deferrable initially immediate)
5 /

Table created.

Example continues on the following page…

Other Text:
(Examples or comments displayed on slide, if any).

Instructor's Notes

(If applicable)

SQL> alter table test disable constraint test_pk;

Table altered.

SQL> insert into test values(1);

1 row created.

SQL> /

1 row created.

SQL> alter table test modify constraint test_pk enable novalidate;

Table altered.

When the DEFERRABLE parameter is set, constraint checking can be delayed (deferred) until the end of a transaction. This is accomplished by using the SET CONSTRAINT(S) statement. (The default is NOT DEFERRABLE, which indicates that the constraint is checked at the end of each DML statement.)
The INITIALLY IMMEDIATE option, the default, performs the constraint test at the end of every DML operation. The INITIALLY DEFERRED option causes the constraint test to be performed only at the end of a transaction (when a commit occurs).
If the original constraint definition has been created as deferrable, the SET CONSTRAINT(S) statement can be used to switch between whether a constraint is checked at the end of each DML statement or at the end of the transaction.
To set the constraint to occur after each DML statement use:
SET CONSTRAINTS ALL IMMEDIATE;
To set the constraint to occur at the end of the transaction use:
SET CONSTRAINTS ALL DEFERRED;
So why would you want to defer your constraint checking un