Previous | Table of Contents | Next

Page 125

19 rows selected.
SQL> drop table temp_employee;
Table dropped.

Defining Table Constraints

Oracle enables the developer to set constraints on tables by using the create table statement. Constraints are important devices that enable you to maintain a strong database. You can use them to ensure that the primary key column(s) are unique to the table and always contain a value. Constraints are also used to maintain referential integrity. This means that the values in a foreign key column must exist as a value in a primary key column of another table. The constraint commands even cause dependant child records to be deleted when the parent record is deleted. Primary and foreign keys are discussed in Chapter 2.

TIP
An imporatant database concept is that it is always better to let the database keep itself clean of bad data than having the applications perform this task. Programming errors are easy to make. The potential always exists that holes in the program logic will allow bad data into the database. Constraints make it much more difficult for this to occur. It is easier to define table constraints than to write and test the code that totally prevents corrupt data.

As an example, suppose you developed an application that allowed the operator to delete a record from a base table that has children's records in a related table. This will cause the related table to have orphan records. This can be prevented by placing logic in the application that prevents the base records from being deleted when children's records exist. This code can be complex. Placing a table constraint in the table definition is easier to write and is much more fool-proof.

Table 6.2 contains valid table constraints that you can use with the create table command.

Table 6.2 Table Constraints

Constraint Description
check Ensures the values meet a specific range.
default Places a default value into the column.
foreign key Ensures the value(s) exists as a value in the primary key column
of another table. When used with the on delete
cascade setting, this constraint causes the record to be deleted when the parent record is deleted.
not null Ensures the value always contains a value.
primary key Ensures the value(s) is not null and unique to the table.
unique Ensures the value is unique to the table.

Page 126

The Check Constraint

The check constraint causes Oracle to evaluate new values that will be recorded in the table against a set of conditions. To define conditions, use the same operators as used in the where clause of the Select statement. The in operator, for example, is commonly used to make certain that the column value matches a set of specified values. Listing 6.5 demonstrates several check constraints. The where clause and conditional operators are discussed in Chapter 4.

Listing 6.5 L_06_05.sql—Defining Check Constraints as Column Constraints

SQL> create table checkexample
  2    (payroll_number       varchar2(10),
  3    gender                char(1) check (gender in (`M', `F')),
  4    next_review_date      date,
  5    pay                number check (pay between 12000 and 16500));

Table created.

SQL> drop table checkexample;

Table dropped.

In this example, the value in the gender column must equal an upper-case `M' or `F.' The pay value must be between 12,000 and 16,500. If the database evaluates any of these check constraints as false, it stops processing the record and issues an error message.

The Default option

The default option causes the rdbms to place a default value into an item when the record is first inserted into the table. As discussed earlier in this book, numeric columns that are null cause problems with column arithmetic unless you use the nvl function. The null value occurred because no value was ever put into the item. You can use the Default command to ensure that numeric items have a default value of 0, thereby eliminating the need for the nvl function. The syntax of the constraint requires the default value to be specified following the default word. Listing 6.6 illustrates the use of this command.

Listing 6.6 L_06_06.TXT—Defining a Default Constraint

SQL> create table defaultexamp
  2    (payroll_number    varchar2(10),
  3     wages             number default(0));

Table created.

SQL> drop table defaultexamp;
Table dropped.

Page 127

The Foreign Key Constraint

The foreign key constraint is extremely useful for maintaining the referential integrity of the database. This constraint ensures the value contained in the column exists as a unique value or primary key in a column of another table. When the foreign key consists of one item, you can define it as a column constraint or as a table constraint. Defining it as a column constrain just require that the keyword references follows the column format. When the column it validates against is a primary key, the table name follows this word. Listing 6.7 illustrates this syntax. When the column validated against is not the primary key, the name of this validated against column must follow the name of the table and be enclosed by parentheses.

Listing 6.7 L_06_07.TXT—Defining a Foreign Key Constraint as a Column Constraint

SQL> create table example
  2    (fk_payroll_number number references employee,
  3     tool_name           varchar2(20));

Table created.

SQL> drop table example;
Table dropped.

The Foreign Key Table ConstraintWhen the foreign key consists of multiple columns, you must define it as a table constant. Table constraints are placed after the last column definition. The definition begins with the words foreign key followed by the name(s) of the column(s) that will be constrained. These names are enclosed by parentheses. This is followed by the word references followed by the table name. As in column definitions, the name of the column validated against is only necessary if it is not the primary key. Listing 6.8 illustrates the definition of a foreign key table constraint clause.

Listing 6.8 L_06_08.TXT—Defining a Foreign Key Constraint as a Table Constraint

SQL> create table foreignkeyexamp
  2    (fk_payroll_number   number,
  3     tool_name            varchar2(20),
  4     foreign key (fk_payroll_number)
  5     references employee (payroll_number));

Table created.

SQL> drop table foreignkeyexamp;
Table dropped.

Previous | Table of Contents | Next