Previous | Table of Contents | Next

Page 128

The On Delete Cascade OptionAnother component of the foreign key constraint is the on delete cascade option. When it is specified, the record is deleted from the table when the row it depends on is deleted in the parent table. This is an important option because it keep orphans from existing in the database. It also relieves the developer from having to write code that deletes records from dependant tables. You should be glad that you can avoid this complex task. Listing 6.9 illustrates the on delete cascade column. This option causes records to be deleted from the ondelete table when the record has a value that does not exist in the employee table.

Listing 6.9 L_06_09.TXT—Defining the On Delete Cascade Option in a Foreign Key Table Constraint

SQL> create table ondelete
  2    (fk_payroll_number      number,
  3     tool_name              varchar2(20),
  4     foreign key (fk_payroll_number)
  5     references employee (payroll_number)
  6     on delete cascade);

Table created.

SQL> drop table ondelete;
Table dropped.
NOTE
One final word on the foreign key constraint concerns the order of creating tables. When the foreign key constraint is placed in the create table statement, Oracle checks to see whether the parent table exists. If it does not, Oracle will not create the table. You must always create the parent tables before the child tables that contain the constraint.

The Not Null Constraint

The not null constraint ensures that the column always contains a value. Oracle will not allow a record to be inserted or updated if the constrained column contains a null value. Proper database design requires the primary key column(s) to always have this constraint. The syntax of this constraint consists of the words not null following the column's data type definition. Listing 6.10 illustrates the definition of a not null constraint.

Listing 6.10 L_06_10.TXT—Defining a Not Null Column Constraint

SQL> create table ondelete
  2    (fk_payroll_number       number,
  3     tool_name               varchar2(20) not null,
  4     foreign key (fk_payroll_number)
  5     references employee (payroll_number)
  6     on delete cascade);

Page 129

Table created.

SQL> drop table ondelete;
Table dropped.

The Unique Constraint

The unique constraint ensures that the value placed in the column row is unique to the table. Proper database design requires that the primary key column(s) always contain unique values. This constraint ensures this will happen. When this constraint is defined, Oracle creates an implicit unique index for the column. Implicit means Oracle7 creates and names the index. Before a record is inserted or updated, Oracle scans the index to determine whether the value exists. The syntax of this constraint is the same as not null; the keyword unique follows the column's data definition. You can specify both the not null and unique constraints in the same column definition. Listing 6.11 demonstrates the definition of the unique constraint. You can also use the constraint on multiple columns. Composite keys are composed of multiple columns, which need to be unique in the combination of their columns when they are also the primary key. The unique constraint ensures this requirement. Be aware, however, of a sixteen-column limit to the number of columns that may be included in the unique constraint.

Listing 6.11 L_06_11.sql—Defining a Unique Constraint as a Column Constraint

SQL> create table ondelete
  2   (fk_payroll_number      number unique,
  3    tool_name              varchar2(20) not null,
  4    foreign key (fk_payroll_number)
  5    references employee (payroll_number)
  6    on delete cascade);

Table created.

SQL> drop table ondelete;

Table dropped.

The Primary Key Constraint

Primary key columns always contain unique and not null values. This means that they need to have both a unique and not null constraint. Oracle has a primary key constraint that defines both of these constraints for you. When the constraint is defined, Oracle creates an implicit unique index on the specified column(s) and defines them not null. When the primary key is one column as it normally is in a base table, you can define the primary key constraint as a column constraint. Listing 6.12 illustrates this. When placing the constraint on primary keys that are also composite keys, you must place the definition as a table constraint. The related or

Page 130

child tables usually have composite primary keys and one of the composite key fields is also a foreign key. Listing 6.13 provides an example of defining a primary key constraint as a table constraint.

Listing 6.12 L_06_12.sql—Defining the Primary Key Constraint as a Column Constraint

SQL> create table example
  2    (payroll_number     number primary key,
  3     wages              number default (0));

Table created.

SQL> drop table example;

Table dropped.

Listing 6.13 L_06_13.TXT—Defining the Primary Key Constraint as a Table Constraint

SQL> create table example
  2    (fk_payroll_number         number,
  3     tool_name                 varchar2(20),
  4     primary key (fk_payroll_number, tool_name),
  5     foreign key (fk_payroll_number)
  6     references employee (payroll_number)
  7     on delete cascade);
Table created.

Disabling Constraints

You may need to disable or drop constraints after they have been defined. To accomplish this, use the alter table command and the disable, enable, and drop options. After the disable option is issued, the referenced constraint is no longer active, but the definition of the constraint remains. You can reactivate it with the enable option. If the drop option is issued, the definition is eliminated from the database. You can add the constraint to the column, but the alter table command will need the full column constraint definition. Listing 6.14 contains examples of several of these options.

Listing 6.14 L_06_14.TXT—Using the Alter Command to Enable, Disable, Add, and Drop Table Constraints

SQL> alter table example disable primary key;

Table altered.

SQL> alter table example enable primary key;

Page 131

Table altered.

SQL> alter table example drop primary key;

Table altered.

SQL> alter table example add primary key
  2   (fk_payroll_number, tool_name);

Table altered.

SQL> drop table example;

Table dropped.

Defining the Index

Indexes are mini tables that contain a subset of one or more of a table's columns. You use the index to increase the performance of queries and to ensure uniqueness of values in the specified columns. Performance is enhanced when Oracle has the capability to scan an ordered index table rather than scan the data in the full table. Indexes ensure uniqueness. Oracle does this by scanning the index to determine whether the value exists prior to placing it in the table. Primary keys, foreign keys, and other columns frequently used to select records should be indexed columns.

You can use the primary key and unique constraints to create implicit indexes. You can implement explicit indexes by using the create index command. The syntax of the statement is the keywords create index followed by your name of the index, followed by the keyword on, the name of the table, and the names of the indexed columns enclosed in parentheses and separated by commas. Listing 6.15 illustrates the definition of an index on the fk_department column of the employee table.

Listing 6.15 L_06_15.TXT—Creating a Table Index

SQL> create index deptind on employee
  2   (fk_department);

Index created.

SQL> drop index deptind;

Index dropped.

You can define a unique index by placing the word unique between the words create index. Listing 6.16 demonstrates this command. It defines a unique index for the department column on the department table. Dropping the table that contains the indexed column or issuing the drop index command destroys indexes. The name of the index follows the command, and a semicolon terminates it.

Previous | Table of Contents | Next