| |
 |
|
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.
CREATE TABLE STUDENTS AS
(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.
|
