Listing 4.29. Creating a CHECK constraint.

SQL> create table Patient (
  2  Patient_ID     varchar2(6) primary key,
  3  Body_Temp_Deg_F  number(4,1) constraint Patient_Body_Temp
  4                   Check (Body_Temp_Deg_F >= 60.0 and
  5                          Body_Temp_Deg_F <= 110.0));
Table created.

SQL> insert into Patient
  2  (Patient_ID, Body_Temp_Deg_F)
  3  values
  4  (`A1001', 98.6);
1 row created.

SQL> insert into Patient
  2  (Patient_ID, Body_Temp_Deg_F)
  3  values
  4  (`Q7777', 111.2);
(`Q7777', 111.2)
              *
ERROR at line 4:
ORA-02290: check constraint (TYO.CK_PATIENT_BODY_TEMP) violated

You can use Oracle built-in SQL functions in a CHECK constraint. As an example, Listing 4.30 displays the definition of a CHECK constraint which verifies that a patient's insurance status is either Y or N.

Listing 4.30. Using the IN operator in a CHECK constraint.

SQL> create table Patient (
  2  Patient_ID     varchar2(6) primary key,
  3  Body_Temp_Deg_F  number(4,1) constraint Patient_Body_Temp
  4                   Check (Body_Temp_Deg_F >= 60.0 and
  5                          Body_Temp_Deg_F <= 110.0),
  6  Insurance_Status Char(1) constraint Patient_Insurance_Status
  7                   Check (Insurance_Status in (`Y','y','N','n')));
Table created.

SQL> insert into Patient
  2  (Patient_ID, Insurance_Status)
  3  values
  4  (`R4321','Y');
1 row created.

SQL> insert into Patient
  2  (Patient_ID, Insurance_Status)
  3  values
  4  (`U3030','U');
(`U3030','U')
             *
ERROR at line 4:
ORA-02290: check constraint (TYO.CK_PATIENT_INSURANCE_STATUS) violated

As you can see, the CHECK constraint is violated when you attempt to specify a value of U for Insurance_Status.

A Column Can Have More Than One CHECK
Constraint

Oracle does not restrict the number of CHECK constraints that can be defined for a column or a table. Back to the Flugle College example, Listing 4.31 demonstrates how a single column—Amount_Approved—has two CHECK constraints defined.

Listing 4.31. Single column with more than one CHECK constraint.

SQL> create table Loan_Application (
  2  Loan_Application_No      number(6) primary key,
  3  Borrower_Last_Name       varchar2(30) not null,
  4  Borrower_First_Name      varchar2(20) not null,
  5  Borrower_Middle_Name     varchar2(20),
  6  Amount_Requested         number(9,2) not null,
  7  Amount_Approved          number(9,2)
  8                           constraint Amount_Approved_Limit
  9                           check (Amount_Approved <= 1000000)
 10                           constraint Amount_Approved_Interval
 11                           check (mod(Amount_Approved,1000)=0)
 12  );
Table created.

SQL> insert into Loan_Application
  2  (Loan_Application_No, Borrower_Last_Name, Borrower_First_Name,
  3   Amount_Requested, Amount_Approved)
  4  values
  5  (2001, `RUBRIK', `STANLEY', 1000000, 999950);
insert into Loan_Application
                   *
ERROR at line 1:
ORA-02290: check constraint (TYO.CK_LOAN_APPLICATION1) violated

SQL> insert into Loan_Application
  2  (Loan_Application_No, Borrower_Last_Name, Borrower_First_Name,
  3   Amount_Requested, Amount_Approved)
  4  values
  5  (2001, `RUBRIK', `STANLEY', 1000000, 999000);
1 row created.

SQL> insert into Loan_Application
  2  (Loan_Application_No, Borrower_Last_Name, Borrower_First_Name,
  3   Amount_Requested, Amount_Approved)
  4  values
  5  (2001, `RUBRIK', `STANLEY', 1000000, 1001000);
insert into Loan_Application
                   *
ERROR at line 1:
ORA-02290: check constraint (TYO.CK_LOAN_APPLICATION2) violated

In the example in Listing 4.31, two constraints are defined for the Loan_Application table. The first constraint (lines 8 and 9) limit the approval amount to one million dollars, whereas the second constraint (lines 10 and 11) ensures that the approval amount is in thousand-dollar increments. You easily could combine both constraints into a single constraint. However, you should consider defining separate constraints if you think you might need to disable a single constraint while allowing other constraints to remain enabled.

Referencing Other Columns in a CHECK Constraint

One of the limitations of a column CHECK constraint is that it cannot reference other columns in the same table. Suppose you're responsible for defining a table for storing loan application information. In this table, Amount_Requested contains the loan amount requested by the borrower; Amount_Approved is the amount that was approved by the loan committee. The lender never approves an amount greater than that requested.

Listing 4.32 illustrates this rule. A column CHECK constraint cannot reference other columns. However, you can use a table constraint to reference any column in a table. By adding a comma after the definition of Amount_Approved, the column constraint becomes a table constraint.

Listing 4.32. Using a table CHECK constraint instead of a column CHECK constraint.

SQL> create table Loan_Application (
  2  Loan_Application_No      number(6) primary key,
  3  Borrower_Last_Name       varchar2(30) not null,
  4  Borrower_First_Name      varchar2(20) not null,
  5  Borrower_Middle_Name     varchar2(20),
  6  Amount_Requested         number(9,2) not null,
  7  Amount_Approved          number(9,2)
  8                           constraint Amount_Approved_Limit
  9                           check (Amount_Approved <= Amount_Requested)
 10  );
)
*
ERROR at line 10:
ORA-02438: Column check constraint cannot reference other columns

SQL> create table Loan_Application (
  2         Loan_Application_No      number(6) primary key,
  3         Borrower_Last_Name       varchar2(30) not null,
  4         Borrower_First_Name      varchar2(20) not null,
  5         Borrower_Middle_Name     varchar2(20),
  6         Amount_Requested         number(9,2) not null,
  7         Amount_Approved          number(9,2),
  8              constraint Amount_Approved_Limit
  9              check (Amount_Approved <= Amount_Requested)
 10  );
Table created.

SQL> insert into Loan_Application
  2  (Loan_Application_No, Borrower_Last_Name, Borrower_First_Name,
  3   Amount_Requested, Amount_Approved)
  4  values
  5  (2001, `CRANDALL', `JULIE', 300000, 310000);
insert into Loan_Application
                   *
ERROR at line 1:
ORA-02290: check constraint (TYO.AMOUNT_APPROVED_LIMIT_) violated

SQL> insert into Loan_Application
  2  (Loan_Application_No, Borrower_Last_Name, Borrower_First_Name,
  3   Amount_Requested, Amount_Approved)
  4  values
  5  (2001, `CRANDALL', `JULIE', 300000, 300000);
1 row created.

Using Pseudocolumns in a CHECK Constraint

A CHECK constraint cannot reference pseudocolumns such as SYSDATE, ROWNUM, and USER. If you need to define a business rule that refers to these pseudocolumns, rely on a database trigger to restrict column values. On Day 11, "More Programming Techniques with PL/SQL," I will pursue this topic in greater detail.

A pseudocolumn is a built-in value (or function without arguments) that returns a specific piece of information by querying a table—usually DUAL. For instance, SYSDATE always returns the current date and time.

Common Restrictions on Modifying
a Table

During the database-design phase, a developer typically experiments with an application's database structures to determine their accuracy and suitability. This task rarely can be done without populating tables with sample data. If a table requires a change—for example, an additional column—altering the table is much more convenient than dropping the table and recreating it with its new definition. However, sometimes the necessary change cannot be made without modifying the sample data.

Changing the Primary Key

Changing a table's primary key without disturbing the rest of a database design is often difficult. The reason is simple: Tables usually are related to one another through the declaration of foreign keys. A foreign key depends upon the existence of a primary key in another table. Therefore, if you change the primary key, the change can ripple throughout the entire database. In fact, Oracle prevents that from happening.