Call (800) 766-1884 for Oracle support & training
Free Oracle Tips

Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance

Free Oracle Tips



BC Oracle tuning

Oracle training

Oracle support

Remote Oracle




Oracle PL/SQL Constraints
Oracle Tips by Burleson

A constraint is a condition placed on a table, typically to satisfy a business rule. When a constraint is placed upon a table, every row in the table must satisfy that constraint. Listing 2.1 provides a definition of the table STUDENTS.

Listing 2.1 A sample table creation script using constraints.

(ssn                    NOT NULL   number(9),
 first_name             NOT NULL   varchar2 (10),
 last_name              NOT NULL   varchar2 (12),
 middle_name                       varchar2 (10),
 street_address         NOT NULL   varchar2 (30),
 apartment_number                  varchar2 (4),
 city                   NOT NULL   varchar2 (30),
 state_code             NOT NULL   varchar2 (2),
 zip_code               NOT NULL   number (5),
 home_phone             NOT NULL   number (10),
 degree_plan                       varchar2 (20),
 overall_gpa                       number (3, 2),
 most_recent_gpa                   number (3, 2));

Notice the columns ssn, first_name, last_name, street_address, city, state_code, and zip_code have the NOT NULL constraint. This constraint requires that each row of data in the STUDENTS table has values for these columns.

How Are Constraints Used?

As previously stated, constraints are often used to enforce business rules. A business rule that all students have a home address, name, and social security number is enforced through the use of the NOT NULL constraint. Several types of constraints exist, including:

  • NOT NULL—A NOT NULL constraint requires that a column contains a value in all rows of a table. If no value is specified for a column with this constraint and no other database functions or objects affect the data before it is added to the table, an error will occur.

  • default—Whenever a NULL value is inserted for the column, it is replaced with the value of the expression specified in the default constraint. For instance, if an hourly salary column is left NULL, it would default to the value of the minimum wage.

  • check—A check constraint allows the Database Administrator (DBA) to specify an expression, which the value in the column must satisfy. If the column does not satisfy the expression, an error will occur. The check constraint is not extremely powerful, but does provide a handy way to enforce simple conditions without using code.

  • unique—A unique constraint specifies that the value of the particular column is unique to a single row inside the table. For instance, in the STUDENTS table, the column ssn would be unique for every student. If you attempt to add a duplicate social security number to the table, an error will occur.

  • primary key—A primary key constraint specifies that the column is part of the table’s primary key. A primary key makes every row within the table unique. A table has only one primary key, which is composed of all the columns that have the primary key constraint. To specify that the value for an individual column must be unique in a table with more than one unique column in the primary key, the unique constraint must be used. Each element of the table’s primary key is, by definition, NOT NULL.

  • foreign key—A foreign key constraint specifies that a value for the specified column(s) must exist as primary key values in another table. For instance, the column degree_plan in the STUDENTS table might have a foreign key reference to the DEGREE_PLANS table’s degree_plan column (a primary key column for the DEGREE_PLANS table). If this is the case, no student can have a degree plan that does not exist in the DEGREE_PLANS table.

The use of constraints to enforce business rules does have limitations. Some business rules are simply too complex to enforce with the limited functionality of the check constraint; in these situations, the system often has to rely on the use of database triggers (which use PL/SQL and can handle complex logic).

A revised description of the STUDENTS table might appear as shown in Listing 2.2.

This is an excerpt from the book "High Performance Oracle Database Automation" by Jonathan Ingram and Donald K. Burleson, Series Editor.


Oracle performance tuning software 

Oracle performance tuning book


Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel
Oracle training & performance tuning books



Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter