0698-0701
Page 698
The UNIQUE constraint does not prevent NULL values from being inserted; it therefore often is used for columns that should be unique but do not necessarily need to be populated . An application might need to store the Social Security numbers for individuals if they are known, for example, while allowing individuals to be inserted before their Social Security numbers are known. If a duplicate exists in this column, it obviously is in error. The column cannot be defined as the primary key, however, because NULL values must be allowed.
The NOT NULL column constraint ensures that NULL values are not inserted into a column, and they can be used with the UNIQUE constraint. Listing 28.5 demonstrates the use of NOT NULL as the sole constraint on a column, as well as its use with a UNIQUE constraint.
Listing 28.5. This DDL script illustrates the uses of NOT NULL column constraints.
CREATE TABLE address_type ( ID NUMBER(10) CONSTRAINT addr_type_pk PRIMARY KEY ,description VARCHAR2(40) CONSTRAINT addr_type_desc_u UNIQUE CONSTRAINT addr_type_desc_nn NOT NULL ,last_updt_id VARCHAR2(40) CONSTRAINT addr_type_lu_id NOT NULL ,last_updt_date DATE CONSTRAINT addr_type_lu_dt NOT NULL );
The NOT NULL constraint is used to simply define required columns, which may or may not be unique. The first and last names may be required to add an individual to the database, for example. Specifying these columns with a NOT NULL constraint ensures that records missing either of these fields are not inserted.
The most flexible column constraint is the CHECK constraint. A CHECK constraint can reference any column in the table, but it cannot reference any external objects, system variables , or system constants. When a CHECK constraint references columns, the conditions always are applied to the current row. To insert or update a column with the CHECK constraint, the specified condition must evaluate to TRUE or unknown (when a NULL value is being inserted into one of the columns referenced by the CHECK condition). CHECK constraints can be used with other column constraints.
CHECK constraints, as the name implies, are particularly useful in sanity checking. Listing 28.6 demonstrates the use of a CHECK constraint on the individual table, which ensures that no individual can be inserted who is more than 150 years old.
Listing 28.6. This DDL script features a CHECK constraint.
CREATE TABLE individual ( ID NUMBER(10) CONSTRAINT indiv_pk PRIMARY KEY ,last_name VARCHAR2(30) CONSTRAINT indiv_l_name NOT NULL
Page 699
,first_name VARCHAR2(30) CONSTRAINT indiv_f_name NOT NULL ,notes VARCHAR2(255) ,date_of_birth DATE CONSTRAINT indiv_chk_bday CHECK (date_of_birth > TO_DATE('01JAN1847', `DDMONYYYY', `nls_date_language = American')) ,last_updt_user VARCHAR2(20) CONSTRAINT indiv_lu_id NOT NULL ,last_updt_date DATE CONSTRAINT indiv_lu_dt NOT NULL ); When used as a column constraint, only one column can be referenced in the CHECK condition, and only one CHECK constraint is allowed per column.
To use the Social Security number as an example again, you might want your application to store the dashes for easy reporting. You can use a CHECK constraint to ensure that the Social Security number is the correct length and that the dashes are in the correct positions .
| TIP |
The system catalog tables, user_indexes and user_constraints, contain all information regarding indexes and constraints. Listing 28.7 contains two simple SQL scripts that query these tables. The scripts can be executed in SQL*Plus or SQL*DBA to produce the output shown in Figure 28.1. |
Listing 28.7. These simple SQL scripts query catalog tables to display information on existing indexes and constraints.
/* INDEXES.SQL */ select table_name, index_name, uniqueness from user_indexes order by 1, 2 / /* CONST.SQL */ select table_name, constraint_name, constraint_type from user_constraints order by 1, 2 /
Page 700
Figure 28.1.
Output generated by
running the SQL scripts
from Listing 28.7 in
SQL*Plus.
You can use column constraints to enforce referential integrity for foreign keys using the REFERENCES keyword. The table and column specified in the REFERENCES clause already must exist, and the referenced column must be defined as a unique key or PRIMARY KEY. Listing 28.9 demonstrates the use of two foreign keys in a table used only to join the referenced objects. Note that the script in Listing 28.8 must be run first, or the second REFERENCES constraint in Listing 28.9 will produce an error.
Listing 28.8. This DDL script creates the company table, which is referenced by a foreign key constraint in Listing 28.9.
CREATE TABLE company ( ID NUMBER(10) CONSTRAINT company_pk PRIMARY KEY ,name VARCHAR2(30) CONSTRAINT co_name_u UNIQUE CONSTRAINT co_name_nn NOT NULL ,notes VARCHAR2(255) ,last_updt_user VARCHAR2(20) CONSTRAINT co_lu_id NOT NULL ,last_updt_date DATE CONSTRAINT co_lu_dt NOT NULL );
Listing 28.9. This DDL script contains two REFERENCES column constraints.
CREATE TABLE ind_co_rel ( individual_id NUMBER(10) CONSTRAINT indco_ind_fk
Page 701
REFERENCES individual(ID) ON DELETE CASCADE CONSTRAINT indco_ind_nn NOT NULL ,company_id NUMBER(10) CONSTRAINT indco_co_fk REFERENCES company(ID) ON DELETE CASCADE CONSTRAINT indco_co_nn NOT NULL ,title VARCHAR2(80) ,last_updt_user VARCHAR2(20) CONSTRAINT indco_lu_id NOT NULL ,last_updt_date DATE CONSTRAINT indco_lu_dt NOT NULL );
The REFERENCES column constraint does not imply NOT NULL, but NOT NULL can be used with a REFERENCES constraint on a column. As a result of the REFERENCES clauses in Listing 28.8, an application cannot insert a row into this table unless both the individual_id and company_id values exist in their respective tables.
| NOTE |
The datatype is unnecessary for columns containing a REFERENCES constraint. The column is defined automatically with the datatype of the column it references. |
The ON DELETE CASCADE option is a very significant feature of the REFERENCES column constraint. If it is not specified, rows with key values in the parent table cannot be deleted until all corresponding rows in the table containing the REFERENCES constraint are deleted. When ON DELETE CASCADE is specified, deletions in the parent table cause all references in the child table to be deleted automatically. In this respect, using ON DELETE CASCADE is similar to creating a DELETE trigger on the referenced table.
| CAUTION |
ON DELETE CASCADE is an extremely powerful option and should be used with caution. If you do not properly understand this option, unwanted automatic deletions could result. |
Although they are ideally suited for enforcing referential integrity, REFERENCES constraints might not be desirable in certain situations. If a table has a high volume of transactions and contains several foreign keys that are simple lookups, for example, performance can be affected adversely by using the REFERENCES constraint. Each time a row is inserted or updated, the referenced tables must be checked to ensure that each foreign key being inserted exists in the referenced tables. Depending on the nature of the data and the importance of performance, it might be preferable to enforce the foreign keys through the application itself.
