Every information system must maintain data and referential integrity. In addition, an information system must also enforce the business rules of the organization that it serves. In the past many information systems were monolithic--;an in-house developed software package accessing a single database residing on a single mainframe computer. Occasionally, third-party utilities were used to access the database.
Today, systems commonly employ a variety of off-the-shelf client tools to access and modify a database. As a result, protecting information at the server level, as well as the client level, is critical. Enforcing security at the client application through menu options and other mechanisms is not sufficient. This chapter explores the use of table and column constraints to preserve data and referential integrity and to enforce business rules.
The discussion begins with a review of the basic steps for defining primary and foreign keys.
You should be sure to define a primary key for every table in your database for the following reasons.
You can define a table's primary key in the CREATE TABLE statement with the following syntax:
CREATE TABLE table-name (column-definition1, ... column-definitionN, [CONSTRAINT constraint-name] PRIMARY KEY (column1,...,columnN))
The variables include the following: table-name is a valid Oracle table name.
column-definition1 through column-definitionN are legal column declarations.
constraint-name is a constraint name that is assigned to the primary
key constraint.
column1 through columnN are the columns that compose the primary
key.
Here is how you define a primary key on the Product table:
SQL> r 1 CREATE TABLE Product 2 (Product_ID VARCHAR2(12) NOT NULL, 3 Manufacturer_ID VARCHAR2(6) NOT NULL, 4 Description VARCHAR2(2000), 5 Date_of_Manufacture DATE, 6 Initial_Retail_Value NUMBER(7,2), 7 Current_Used_Value NUMBER(7,2), 8 Replacement_Product VARCHAR2(30), 9 Created_Date DATE, 10 Created_By VARCHAR2(30), 11 Modified_Date DATE, 12 Modified_By VARCHAR2(30), 13 Constraint Product_PK Primary Key (Product_ID, Manufacturer_ID) 14* ); Table created.
The following SQL statements illustrate how the primary key prevents duplicate rows from being inserted into the table. A duplicate row is defined as a row whose primary key columns have the same values as those of another row. The following code reveals that the primary key is enforced, even though the first INSERT transaction hasn't been committed.
SQL> insert into Product
2 (Product_ID, Manufacturer_ID, Initial_Retail_Value)
3 values
4 ('B101', KANIN', 500);
1 row created.
SQL> insert into Product
2 (Product_ID, Manufacturer_ID, Initial_Retail_Value)
3 values
4 ('B101','KANIN',600);
insert into Product
*
ERROR at line 1:
ORA-00001: unique constraint (FRAYED_WIRES.PRODUCT_PK) violated
You also have the option of defining the primary key after the table has been created.
TipIf you don't provide a constraint name, Oracle automatically creates constraint names for each of the table and column constraints that you define. The Oracle-generated names are fairly cryptic, and you won't know what they are unless you query the Oracle data dictionary view named USER_CONSTRAINTS. In several situations, you need to know a constraint name. For example, when you want to drop a foreign key, you'll need to drop the constraint associated with the foreign key. Unless you've supplied the constraint name for the foreign key, you'll have to look up the constraint name. Therefore, providing Oracle with constraint names for primary and foreign keys is always a good idea. Consider naming primary key constraints as tablename_PK and foreign key constraints as tablename_column_FK, staying within the 30-character limit for Oracle object names. You should also specify names for check and unique constraints (for example, tablename_column_CK or tablename_column_UN).
To define a primary key constraint for an existing table, use the ALTER TABLE statement.
SQL> CREATE TABLE Product 2 (Product_ID VARCHAR2(12) NOT NULL, 3 Manufacturer_ID VARCHAR2(6) NOT NULL, 4 Description VARCHAR2(2000), 5 Date_of_Manufacture DATE, 6 Initial_Retail_Value NUMBER(7,2), 7 Current_Used_Value NUMBER(7,2), 8 Replacement_Product VARCHAR2(30), 9 Created_Date DATE, 10 Created_By VARCHAR2(30), 11 Modified_Date DATE, 12 Modified_By VARCHAR2(30)); Table created. SQL> alter table Product add 2 Constraint Product_PK Primary Key (Product_ID, Manufacturer_ID); Table altered.
If the existing table contains duplicate rows, Oracle returns an error message when you attempt to define the primary key.
SQL> insert into Product
2 (Product_ID, Manufacturer_ID, Initial_Retail_Value)
3 values
4 ('B101','KANIN', 500);
1 row created.
SQL> insert into Product
2 (Product_ID, Manufacturer_ID, Initial_Retail_Value)
3 values
4 ('B101','KANIN', 500);
1 row created.
SQL> alter table Product add
2 Constraint Product_PK Primary Key (Product_ID, Manufacturer_ID);
alter table Product add
*
ERROR at line 1:
ORA-02299: cannot add or enable constraint (FRAYED_WIRES.PRODUCT_PK)-
duplicate keys found
Primary and foreign keys work together to enforce referential integrity. A foreign key in a table is a column, or set of columns, whose values are restricted to those of the primary key in another table. You should define foreign keys whenever possible. For client/server applications, the first line of defense for referential integrity is the client application software. The last line of defense for referential integrity is the primary and foreign keys that have been defined for the database.
As with a primary key, a foreign key can be declared when a table is first created.
To illustrate the definition of a foreign key, refer to the Product and Manufacturer tables in the Frayed Wires sample database. Each manufacturer is assigned a unique ID that resides in the Manufacturer_ID column. To address the possibility that two manufacturers could use the same product ID, you can store Manufacturer_ID in the Product table. To uniquely identify rows in the Product table, declare the primary key to be Product_ID and Manufacturer_ID.
SQL> CREATE TABLE Manufacturer
2 (Manufacturer_ID VARCHAR2(6) NOT NULL,
3 Manufacturer_Name VARCHAR2(30) NOT NULL,
4 Street_Address VARCHAR2(60),
5 City VARCHAR2(30),
6 State VARCHAR2(2),
7 Zipcode VARCHAR2(9),
8 Telephone_Number VARCHAR2(10),
9 Fax_Number VARCHAR2(10),
10 Created_Date DATE NOT NULL,
11 Created_By VARCHAR2(30) NOT NULL,
12 Modified_Date DATE,
13 Modified_By VARCHAR2(30),
14 Constraint Manufacturer_PK Primary Key (Manufacturer_ID));
Table created.
SQL> CREATE TABLE Product
2 (Product_ID VARCHAR2(12) NOT NULL,
3 Manufacturer_ID VARCHAR2(6) NOT NULL
4 Constraint Manufacturer_ID_FK
References Manufacturer(Manufacturer_ID),
5 Description VARCHAR2(2000),
6 Date_of_Manufacture DATE,
7 Initial_Retail_Value NUMBER(7,2),
8 Current_Used_Value NUMBER(7,2),
9 Replacement_Product VARCHAR2(30),
10 Created_Date DATE,
11 Created_By VARCHAR2(30),
12 Modified_Date DATE,
13 Modified_By VARCHAR2(30),
14 Constraint Product_PK Primary Key (Product_ID, Manufacturer_ID));
Table created.
After the foreign key has been defined, referential integrity is enforced.
SQL> insert into Product
2 (Product_ID, Manufacturer_ID, Description)
3 values
4 ('X1000','SCH100','VCR, Stereo-HiFi, 4-Head');
insert into Product
*
ERROR at line 1:
ORA-02291: integrity constraint (FRAYED_WIRES.MANUFACTURER_ID_FK)
violated - parent key not found
As an alternative, you can opt not to specify the datatype for a column that is a foreign key. The next example doesn't include VARCHAR2(6) in the declaration of Manufacturer_ID. Instead, Oracle looks up the datatype and width for the Manufacturer_ID column in the Manufacturer table and uses those definitions when creating the Product table.
SQL> CREATE TABLE Product
2 (Product_ID VARCHAR2(12) NOT NULL,
3 Manufacturer_ID NOT NULL
4 Constraint Manufacturer_ID_FK
References Manufacturer(Manufacturer_ID),
5 Description VARCHAR2(2000),
6 Date_of_Manufacture DATE,
7 Initial_Retail_Value NUMBER(7,2),
8 Current_Used_Value NUMBER(7,2),
9 Replacement_Product VARCHAR2(30),
10 Created_Date DATE,
11 Created_By VARCHAR2(30),
12 Modified_Date DATE,
13 Modified_By VARCHAR2(30),
14 Constraint Product_PK Primary Key (Product_ID, Manufacturer_ID));
Table created.
SQL> desc Product
Name Null? Type
------------------------------- -------- ----
PRODUCT_ID NOT NULL VARCHAR2(12)
MANUFACTURER_ID NOT NULL VARCHAR2(6)
DESCRIPTION VARCHAR2(2000)
DATE_OF_MANUFACTURE DATE
INITIAL_RETAIL_VALUE NUMBER(7,2)
CURRENT_USED_VALUE NUMBER(7,2)
REPLACEMENT_PRODUCT VARCHAR2(30)
CREATED_DATE DATE
CREATED_BY VARCHAR2(30)
MODIFIED_DATE DATE
MODIFIED_BY VARCHAR2(30)
The advantage of not declaring a datatype for a foreign key column is that the foreign key column will have the same datatype definition as its primary key counterpart. The disadvantage is that you can't determine the foreign key column's datatype by examining the CREATE TABLE statement.
As an alternative to declaring a foreign key when you create a table, you can declare a foreign key on an existing table with the ALTER TABLE statement.
SQL> create table AP_Detail ( 2 Bill_Number NUMBER(4) NOT NULL, 3 Vendor_Invoice_Number VARCHAR2(10) NOT NULL, 4 Item_Number NUMBER(3) NOT NULL, 5 Billed_Amount NUMBER(8,2) NOT NULL, 6 Approved_Amount NUMBER(8,2), 7 Paid_Amount NUMBER(8,2)); Table created. SQL> alter table AP_Detail add constraint AP_Detail_Vendor_Inv_Num_FK 2 foreign key (Vendor_Invoice_Number) references AP_Header; Table altered.
When you define a foreign key, Oracle verifies the following:
As an example, suppose that you have two tables: Employee and Employee_Dependent. The primary key of the Employee table is Employee_ID. Of course, the Employee_ Dependent table also contains Employee_ID.
SQL> desc employee Name Null? Type ------------------------------- -------- ---- EMPLOYEE_ID NOT NULL NUMBER(4) LAST_NAME NOT NULL VARCHAR2(30) FIRST_NAME NOT NULL VARCHAR2(20) MIDDLE_INITIAL CHAR(1) HIRE_DATE NOT NULL DATE TERMINATION_DATE DATE DATE_OF_BIRTH DATE MONTHLY_SALARY NUMBER(5) MANAGER NOT NULL CHAR(1) USERNAME VARCHAR2(31) SQL> desc employee_dependent Name Null? Type ------------------------------- -------- ---- EMPLOYEE_ID NOT NULL VARCHAR2(4) LAST_NAME VARCHAR2(30) FIRST_NAME VARCHAR2(20) MIDDLE_INITIAL CHAR(1) RELATIONSHIP VARCHAR2(30)
Observe that the Employee table's definition for Employee_ID is NUMBER(4), but the Employee_Dependent table's definition for Employee_ID is VARCHAR2(4). As a result, Oracle does not allow the foreign key to be defined for Employee_ID in Employee_Dependent, which references Employee_ID in Employee.
SQL> alter table Employee_Dependent add Constraint Employee_Dependent_FK1
2 Foreign Key (Employee_ID) references Employee;
Foreign Key (Employee_ID) references Employee
*
ERROR at line 2:
ORA-02256: number, type and size of referencing columns must match
referenced columns
As I've mentioned previously, primary and foreign key constraints enforce two crucial aspects of the relational model: data and referential integrity. You may want to disable these constraints for at least two tasks--;the task of designing the database and the task of migrating the organization's legacy data.
A database is usually designed for an organization that has existing information, both computer-based and paper-based information. The database design process is not a one-time task; rather, it is iterative. Regardless of how simple or complex the organization's operations may be, the analysis and design process can be boiled down to these steps:
During this process, you will want to look at legacy data to determine if your logical data model is appropriate. The terms legacy system and legacy data refer to the existing information system and its data. The quality of the legacy data depends on how well legacy application software has enforced data and referential integrity. The legacy data may be very clean; that is, it doesn't have any duplicate records or illegal field values. However, clean legacy data is the exception, rather than the rule, in the vast majority of cases. Most legacy data contains some duplicate records. Field values may not correspond to the meaning of the field--;for example, an N/A in a field used to store order quantity. Referential integrity may be violated in numerous instances. As a result, loading legacy data into an Oracle database in which data and referential integrity are properly enforced isn't feasible. Consider the following example.
Suppose you've designed a set of Oracle tables to support accounts payable operations. Two of the tables are
The legacy system has files that correspond to the AP_Header and Vendor tables.
You've already loaded the contents of APMASTR and VENDORS into intermediate Oracle tables--;that is, tables that are structurally equivalent to the legacy files. Here are the contents of the intermediate tables:
SQL> select BILLNO, VENDID, VINVNO
2 from APMASTR;
BILLNO VENDID VINVNO
--------- ------ ----------
1001 M202 AG7001
1002 S501 TR2111
1003 G309 QE03911
1004 T703 WE09834
9101 N/A N/A
SQL> select VENDID, COMPNAME
2 from VENDORS;
VEND COMPNAME
---- -----------------------------------------
M202 METATRON INDUSTRIES
S501 SOLIPSISM INC.
G309 GOLIATH BUSINESS SYSTEMS CO.
T703 TRANSWORLD STONE INC.
Now that the intermediate Oracle tables have been loaded, you decide to insert the rows into the final tables--;AP_Header and Vendor.
SQL> insert into Vendor 2 (Vendor_ID, Company_Name) 3 select VENDID, COMPNAME 4 from VENDORS; 4 rows created. SQL> commit; Commit complete. SQL> insert into AP_Header 2 (Bill_Number, Vendor_ID, Vendor_Invoice_Number) 3 select BILLNO, VENDID, VINVNO 4 from APMASTR; insert into AP_Header * ERROR at line 1: ORA-02291: integrity constraint (FRAYED_WIRES.AP_HEADER_VENDOR_ID_FK) violated - parent key not found
When you look at the contents of APMASTR, the reason that Oracle rejected the records is evident. For Bill_Number 9101, the Vendor_ID is listed as N/A--;not applicable, as documented in the legacy data. However, the Vendor table doesn't have a row in which Vendor_ID is N/A. Accordingly, the INSERT statement violates the referential integrity constraint, and the rows aren't inserted into AP_Header.
In reality, hundreds or thousands of legacy records could violate declared constraints. As a temporary measure, you can disable these constraints in this manner:
SQL> alter table AP_Header disable constraint AP_Header_Vendor_ID_FK; Table altered. SQL> insert into AP_Header 2 (Bill_Number, Vendor_ID, Vendor_Invoice_Number) 3 select BILLNO, VENDID, VINVNO 4 from APMASTR; 5 rows created. SQL> commit; Commit complete.
After the bad data has been corrected or removed, you can enable the constraint.
SQL> delete from AP_Header
2 where Bill_Number = 9101;
1 row deleted.
SQL> alter table AP_Header enable constraint AP_Header_Vendor_ID_FK;
Table altered.
SQL> select Bill_Number, Vendor_ID, Vendor_Invoice_Number
2 from AP_Header;
BILL_NUMBER VENDOR VENDOR_INV
----------- ------ ----------
1001 M202 AG7001
1002 S501 TR2111
1003 G309 QE03911
1004 T703 WE09834
Another way to capture and correct records that violate a constraint is to use an Exceptions table. When you specify the constraint in a CREATE TABLE or ALTER TABLE statement, you can also specify a table to store the ROWIDs of rows that violate the constraint. You can use a SQL script in C:\Orawin95\rdbms72\admin\utlexcpt.sql to create an Exceptions table.
For example, suppose the Demo_Exceptions table doesn't have a primary key.
SQL> select ssn, last_name from demo_exceptions; SSN LAST_NAME ----------- ------------------------------ 321-45-6789 JOHNSON 123-45-6789 SMITH 321-45-6789 JONES
As you can see, the rows for JOHNSON and JONES have the same Social Security number. When you add a primary key to the table, you can specify an EXCEPTIONS clause that will store the ROWID of each row that violates the primary key constraint.
SQL> alter table demo_exceptions 2 add constraint demo_exceptions_PK 3 primary key (ssn) 4 exceptions into EXCEPTIONS; alter table demo_exceptions * ERROR at line 1: ORA-02299: cannot add or enable constraint (FRAYED_WIRES.DEMO_EXCEPTIONS_PK)- duplicate keys found
The Exceptions table contains information about the rows that violated the constraint.
SQL> select row_id, constraint 2 from exceptions; ROW_ID CONSTRAINT ------------------ ------------------------------ 00000E65.0000.0001 DEMO_EXCEPTIONS_PK 00000E65.0002.0001 DEMO_EXCEPTIONS_PK
You can use the ROWID from the Exceptions table to point to the row in the Demo_Exceptions table that violated the constraint.
SQL> select * from demo_exceptions 2 where 3 rowid = '00000E65.0000.0001'; SSN LAST_NAME ----------- ------------------------------ 321-45-6789 JOHNSON
You then modify JOHNSON's Social Security number and successfully add the primary key to the table.
SQL> update demo_exceptions 2 set ssn = '321-45-9876' 3 where 4 last_name = 'JOHNSON'; 1 row updated. SQL> alter table demo_exceptions 2 add constraint demo_exceptions_PK 3 primary key (ssn) 4 exceptions into EXCEPTIONS; Table altered.
During the database design process, you may need to drop a table's primary key. The syntax for the ALTER TABLE statement is
ALTER TABLE table-name DROP PRIMARY KEY;
where table-name is the table associated with the primary key.
For the sake of illustration, suppose you create a table for the storage of accounts payable data and define the primary key to be the Vendor's invoice number.
SQL> create table AP_Header ( 2 Bill_Number NUMBER(4) NOT NULL, 3 Vendor_Invoice_Number VARCHAR2(10) NOT NULL, 4 Vendor_ID VARCHAR2(6) NOT NULL, 5 Date_Received DATE NOT NULL, 6 Bill_Status VARCHAR2(5), 7 PRIMARY KEY (Vendor_Invoice_Number)); Table created.
After inspecting some data and pondering this definition, you realize that two different vendors could easily supply the same invoice number. Therefore, you drop the primary key for the table.
SQL> alter table AP_Header drop primary key; Table altered.
What if you've already declared a foreign key in the AP_Detail table that references the primary key of AP_Header? If such cases, Oracle does not allow you to drop AP_Header's primary key.
SQL> create table AP_Detail ( 2 Bill_Number NUMBER(4) NOT NULL, 3 Vendor_Invoice_Number VARCHAR2(10) NOT NULL, 4 Item_Number NUMBER(3) NOT NULL, 5 Billed_Amount NUMBER(8,2) NOT NULL, 6 Approved_Amount NUMBER(8,2), 7 Paid_Amount NUMBER(8,2), 8 Constraint AP_Detail_FK Foreign Key (Vendor_Invoice_Number) 9 References AP_Header); Table created. SQL> alter table AP_Header drop primary key; alter table AP_Header drop primary key * ERROR at line 1: ORA-02273: this unique/primary key is referenced by some foreign keys
You can use the keyword CASCADE as an option in the DROP PRIMARY KEY clause, but use this feature with caution! CASCADE drops the primary key as well as any foreign keys that reference it.
SQL> alter table AP_Header drop primary key cascade; Table altered.
During the database design process, you may find that you've mistakenly defined a column as a foreign key. Dropping a foreign key is a bit different than dropping a primary key. Because a table can have more than one foreign key, the ALTER TABLE statement requires you to supply the constraint name associated with the foreign key. The syntax is
ALTER TABLE table-name DROP CONSTRAINT constraint-name;
where table-name is the table associated with the primary key and constraint-name is the constraint associated with the foreign key.
For instance, suppose that the AP_Header table has an additional column named Vendor_Status, which, coincidentally, has the same datatype and width as Vendor_ID. You mistakenly create a foreign key for Vendor_Status that references the primary key of the Vendor table.
SQL> desc AP_Header Name Null? Type ------------------------------- -------- ---- BILL_NUMBER NOT NULL NUMBER(4) VENDOR_INVOICE_NUMBER NOT NULL VARCHAR2(10) VENDOR_ID NOT NULL VARCHAR2(6) DATE_RECEIVED NOT NULL DATE BILL_STATUS VARCHAR2(5) VENDOR_STATUS VARCHAR2(6) SQL> alter table AP_Header add constraint AP_Header_Vendor_Status_FK 2 foreign key (Vendor_Status) references Vendor; Table altered.
After you try to insert a value into the Vendor_Status column, you quickly realize your error and drop the foreign key assigned to the column.
SQL> alter table AP_Header drop constraint AP_Header_Vendor_Status_FK; Table altered.
Whereas primary and foreign key constraints enforce data and referential integrity, another type of constraint enforces a unique set of values on a column. (A unique constraint is not a substitute for a primary key constraint.) As an example, most organizations assign a number to each employee--;an employee number. In addition, each employee also has a Social Security number, which is unique. But the Social Security number is an attribute of an employee, whereas the purpose of the Employee_ID is to uniquely identify rows in the Employee table.
SQL> create table Patient (
2 Patient_ID varchar2(6) primary key,
3 Last_Name varchar2(30) not null,
4 First_Name varchar2(20) not null,
5 Middle_Name varchar2(20),
6 Social_Security_Number varchar2(9) unique,
7 Insurance_Carrier_Code varchar2(4));
Table created.
SQL> insert into Patient
2 (Patient_ID, Last_Name, First_Name)
3 values
4 ('A901', 'NORTON', 'ED');
1 row created.
SQL> insert into Patient
2 (Patient_ID, Last_Name, First_Name, Social_Security_Number)
3 values
4 ('A902', 'KRAMDEN', 'RALPH', '123456789');
1 row created.
SQL> insert into Patient
2 (Patient_ID, Last_Name, First_Name, Social_Security_Number)
3 values
4 ('A903', 'NORTON', 'TRIXIE', '123456789');
insert into Patient
*
ERROR at line 1:
ORA-00001: unique constraint (FRAYED_WIRES.SYS_C00550) violated
Several differences between primary key and unique constraints are worth noting. First, a table can have only one primary key--;but it can have many unique constraints. Second, when a primary key is defined, the columns that compose the primary key are automatically mandatory. When a unique constraint is declared, the columns that compose the unique constraint are not automatically defined to be mandatory; you must also specify that the column is NOT NULL.
Any discussion of primary and foreign key constraints must also examine indexes--;the topics are closely related. A table index is an Oracle object that contains the values that exist in one or more columns in a table. This section describes table indexes and explains how they are used by both the application developer and Oracle.
Oracle provides two types of table indexes: unique and nonunique. Unique indexes enforce primary key and unique constraints. Nonunique indexes improve query performance, which is discussed further in Chapter 33, "Oracle Internals." Both types of indexes are implemented internally via a B*-tree data structure. A B*-tree data structure is graphically depicted as a balanced, inverted tree in which each leaf represents an index value. Understanding the following concepts is critical when you design an application's database:
The basic syntax for creating an index is
CREATE [UNIQUE] INDEX index-name ON table-name (column1, ... columnN);
where index-name is the name to be given to the index (subject to Oracle database object naming restrictions), table-name is the table for which the index is created, and column1 through columnN are the columns to be used in creating the index.
Notice that the keyword UNIQUE is optional. If you don't include UNIQUE, the created index is nonunique. In other words, the nonunique index does not restrict the values of a set of columns in any way. If you include the keyword UNIQUE, the index prevents a duplicate set of column values from being stored in the table.
Here's a straightforward example. The primary key of the Employee table is Employee_ID. However, you'll frequently query the Employee table based on an employee's last name. To improve the performance of those queries, you create an index.
SQL> create index Employee_Last_Name on Employee (Last_Name); Index created.
Although CREATE UNIQUE INDEX is a legal Oracle statement, you shouldn't use it; instead, declare PRIMARY KEY and UNIQUE constraints. The two principal reasons for this advice are
If you're using Personal Oracle7 for Windows 95, you can use the Navigator to examine, create, and drop indexes. To see a list of all the indexes in your local database, double-click the Index folder--;the list of indexes appears on the right side of the Navigator main window. (See Figure 14.1.)
You can sort the list of indexes alphabetically by name--;click the Name column heading--;or by owner--;click the Owner column heading.
If you want to see the properties of a particular index, select the index and right-click Properties. A window displays the name of the index, the owner of the index, the name of the table on which the index is based, the columns that compose the index, and the columns that are a part of the index. (See Figure 14.2.)
NoteBe aware that the Navigator will display all the table's columns in the Index Properties window. However, only those columns with a numeric value in the Order column heading compose the index. For example, a single column with an Order value of 1 indicates that the index is based on that column only.
To create a new index, select the Index folder and right-click New. The Navigator displays a window in which you specify the index name, index owner, table name, and column information. (See Figure 14.3.)
Enter an appropriate name for the index and select the index owner from the list of all users by pressing the down arrow in the owner field. Also, select the table to be indexed by pressing the down arrow in the table field. Finally, indicate which columns compose the index and their order by clicking the order field to the right of the desired columns. If you have mistakenly specified an order for a column, you can clear it by double-clicking the Order field. Figure 14.3 shows an index on the Loan_Application table based on Borrower_Last_Name and Borrower_First_Name--;in that order.
A table can have multiple indexes as long as each index is based on a different set of columns from the other indexes. For example, you could create another index on the Loan_Application table in which you reversed the order of the two columns, Borrower_First_Name and Borrower_Last_Name as shown in Figure 14.4.
However, if you try to create another index with the same column list as an existing index, Personal Oracle7 will return an error message. (See Figure 14.5.)
To delete an index, select the index to be deleted and right-click Delete. The Navigator will prompt you to confirm the deletion. (See Figure 14.6.)
The syntax to delete an index with SQL*Plus is
DROP INDEX [owner.]index_name
where index_name is the name of the index to be dropped and owner is the name of the Oracle account that owns the index. This parameter is optional; if owner is specified, the user dropping the index must have the DROP ANY INDEX system privilege or have been granted the privilege to drop the specified index.
When you declare a column as NOT NULL, Oracle treats the mandatory requirement as a constraint. In fact, this constraint is the only one that can be seen with the SQL*Plus DESCRIBE command. Here is an illustration:
SQL> describe employee Name Null? Type ------------------------------- -------- ---- EMPLOYEE_ID NOT NULL NUMBER(4) LAST_NAME NOT NULL VARCHAR2(30) FIRST_NAME NOT NULL VARCHAR2(20) MIDDLE_INITIAL CHAR(1) HIRE_DATE NOT NULL DATE TERMINATION_DATE DATE DATE_OF_BIRTH DATE MONTHLY_SALARY NUMBER(5) MANAGER NOT NULL CHAR(1) USERNAME VARCHAR2(31)
The DESCRIBE command shows that the Employee table has five mandatory columns. This number is somewhat misleading because Employee_ID was defined as the table's primary key--;which is automatically NOT NULL. You can use the Oracle data dictionary view named USER_CONSTRAINTS to see the constraints associated with a table. The columns returned by USER_CONSTRAINTS include
Following are the results of a query of USER_CONSTRAINTS for all constraints associated with the Employee table: four NOT NULL constraints, a CHECK constraint on the Manager column, and a primary key constraint that is indicated by a value of P for Constraint_Type.
SQL> select constraint_name, constraint_type, search_condition
2 from user_constraints
3 where
4 table_name = 'EMPLOYEE';
CONSTRAINT_NAME CONST SEARCH_CONDITION
------------------------------ ----- ----------------------------------------
SYS_C00517 C LAST_NAME IS NOT NULL
SYS_C00518 C FIRST_NAME IS NOT NULL
SYS_C00519 C HIRE_DATE IS NOT NULL
SYS_C00520 P
SYS_C00521 C Manager in ('Y','N')
SYS_C00522 C MANAGER IS NOT NULL
6 rows selected.
You can specify a constraint name for a NOT NULL constraint in this manner:
SQL> create table demo_not_null ( 2 my_column number constraint Value_Required not null); Table created. SQL> select constraint_name, constraint_type, search_condition 2 from user_constraints 3 where 4 table_name = 'DEMO_NOT_NULL'; CONSTRAINT_NAME CONST SEARCH_CONDITION ------------------------------ ----- ---------------------------------------- VALUE_REQUIRED C MY_COLUMN IS NOT NULL
If you wanted to change the column to allow NULL values, you would use the ALTER TABLE statement in the following way:
SQL> alter table demo_not_null modify 2 (my_column number null); Table altered. SQL> select constraint_name, constraint_type, search_condition 2 from user_constraints 3 where 4 table_name = 'DEMO_NOT_NULL'; no rows selected
As you can see from the previous query, the NOT NULL constraint no longer exists.
The CHECK constraint is a column-level constraint that serves at least two purposes.
The CHECK constraint is declared in a CREATE TABLE or ALTER_TABLE statement using this syntax:
column-name datatype [CONSTRAINT constraint-name] [CHECK (condition)]
The variables are defined in this way: column-name is the column name.
datatype is the column's datatype, width, and scale.
constraint-name is the constraint name subject to Oracle database object naming restrictions.
condition is a legal Oracle SQL condition that returns a Boolean value.
To illustrate this concept, you can create a table that a hospital database uses
to store patient information. One of the columns in this table is the patient's body
temperature in degrees Fahrenheit. You should restrict the possible values of this
column by defining it as NUMBER(4,1). But this column still accepts numbers from
0.0 to 999.9--;including some obviously nonsensical values for body temperature.
You can use a CHECK constraint to restrict the value to a range of 60.0
(for patients suffering from hypothermia) to 110.0.
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 (FRAYED_WIRES.PATIENT_BODY_TEMP) violated
You can use Oracle built-in SQL functions in a CHECK constraint. As an example, here is a CHECK constraint that verifies that a patient's insurance status is either Y or N:
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 (FRAYED_WIRES.PATIENT_INSURANCE_STATUS) violated
Personal Oracle7 does not restrict the number of CHECK constraints that can be defined for a column or a table. The following code is an example of a column--;Amount_Approved--;that has two constraints:
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 (FRAYED_WIRES.AMOUNT_APPROVED_INTERVAL) 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 (FRAYED_WIRES.AMOUNT_APPROVED_LIMIT) violated
In this example you can easily 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.
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. Nevertheless, you can't use a column CHECK constraint to enforce this business rule.
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
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.
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 (FRAYED_WIRES.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.
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. Chapter 23, "Enforcing Business Rules with Database Triggers," explains this technique.
Referential integrity is enforced for all DML statements: INSERT, UPDATE, and DELETE. For example, if you attempt to update a table so that a foreign key is set to a value that doesn't exist as a primary key in the referenced table, Oracle rejects the update.
SQL> update Repair_Item
2 set
3 Repair_ID = 509
4 where
5 Repair_ID = 506;
update Repair_Item
*
ERROR at line 1:
ORA-02291: integrity constraint (FRAYED_WIRES.REPAIR_ITEM_REPAIR_ID_FK)
violated - parent key not found
The next section discusses the effect of UPDATE and DELETE on a primary key value.
The default behavior of Oracle is to prevent changes to a primary key value if that primary key value has children. The children are the rows in other tables whose foreign key values refer to the primary key value to be changed. Using the repair store example, observe what happens if you try to delete Repair ID 505 from the Repair_Header table.
SQL> set linesize 132 SQL> delete from Repair_Header 2 where 3 Repair_ID = 505; delete from Repair_Header * ERROR at line 1: ORA-02292: integrity constraint (FRAYED_WIRES.REPAIR_ITEM_REPAIR_ID_FK) violated - child record found
Oracle rejects the DELETE statement because the Repair_Item table has one or more rows in which Repair ID is equal to 505. Similarly, Oracle also rejects an UPDATE statement that attempts to change the value of Repair ID from 505 to 999.
SQL> update Repair_Header
2 set Repair_ID = 999
3 where
4 Repair_ID = 505;
update Repair_Header
*
ERROR at line 1:
ORA-02292: integrity constraint (FRAYED_WIRES.REPAIR_ITEM_REPAIR_ID_FK)
violated - child record found
Oracle provides an option for a foreign key constraint that causes the deletion of a primary key value to cascade to any child records that reference that value. To use the previous example, if Repair ID 505 was deleted from the Repair_Header table, the DELETE CASCADE option would cause the rows in the Repair_Item table in which Repair ID was 505 to also be deleted. The syntax for this feature is
SQL> alter table Repair_Item 2 add constraint Repair_Item_Repair_ID_FK 3 foreign key (Repair_ID) references Repair_Header 4 on delete cascade; Table altered.
Now, when Repair ID is deleted from Repair_Header, its child rows in Repair_Item are deleted automatically.
SQL> delete from Repair_Header 2 where 3 Repair_ID = 505; 1 row deleted. SQL> select Repair_ID 2 from Repair_Item 3 where 4 Repair_ID = 505; no rows selected
Remember the following concepts when defining table and column constraints: