You can use the CREATE TABLE statement to enforce several different kinds of constraints on a table: candidate keys, primary keys, foreign keys, and check conditions. A CONSTRAINT clause can constrain a single column or group of columns in a table. The point of these constraints is to get Oracle to do most of the work in maintaining the integrity of your database. The more constraints you add to a table definition, the less work you have to do in applications to maintain the data. On the other hand, the more constraints there are in a table, the longer it takes to update the data.
You can specify constraints in one of two ways: as part of the column definition (known as a column constraint) or at the end of the CREATE TABLE statement (known as a table constraint). Clauses that constrain several columns must be table constraints.
The Candidate Key
A candidate key is a combination of one or more columns, the values of which uniquely identify each row of a table. The following listing shows the creation of a UNIQUE constraint for the TROUBLE table:
The key for this table is the combination of CITY and SAMPLE_DATE. Notice that both columns are also declared to be NOT NULL. This feature requires you to specify values for certain columns in order for rows to be inserted. Clearly, temperature and precipitation information is not useful without knowing where or when it was collected. This technique is common for columns that are the table’s primary key, but it’s also useful if certain columns are critical for the row of data to be meaningful. If NOT NULL isn’t specified, the column can have NULL values.
When you create a UNIQUE constraint, Oracle creates a unique index to enforce the uniqueness of the values. Within a primary key constraint, at least one of the columns in the primary key for each row must be NOT NULL.
The Primary Key
The primary key of a table is one of the candidate keys that you give some special characteristics. You can have only one primary key, and a primary key column cannot contain NULLs.
The following CREATE TABLE statement has the same effect as the previous one, except that you can have several UNIQUE constraints but only one PRIMARY KEY constraint:
For single-column primary or candidate keys, you can define the key on the column with a column constraint instead of a table constraint:
In this case, the AUTHOR_NAME column is the primary key, and Oracle will generate a name for the PRIMARY KEY constraint. This is not recommended if you want to enforce a common naming standard for keys, as discussed later in “Naming Constraints.”
Designating Index Tablespaces
UNIQUE and PRIMARY KEY constraints create indexes. Unless you tell Oracle differently, those indexes are placed in your default tablespace. To specify a different tablespace, use the USING INDEX TABLESPACE clause of the CREATE TABLE command, as shown in the following listing:
The index associated with the AUTHOR_PK primary key constraint will be placed in the USERS tablespace.
NOTE
In most default installations, the USERS tablespace is created and is the default tablespace.
The Foreign Key
A foreign key is a combination of columns with values based on the primary key values from another table. A foreign key constraint, also known as a referential integrity constraint, specifies that the values of the foreign key correspond to actual values of the primary key in the other table. In the BOOKSHELF table, for example, the CATEGORY_NAME column refers to values for the CATEGORY_NAME column in the CATEGORY table:
You can refer to a primary or unique key, even in the same table. However, you can’t refer to a table in a remote database in the REFERENCES clause. You can use the table form (which is used earlier to create a PRIMARY KEY on the TROUBLE table) instead of the column form to specify foreign keys with multiple columns.
Sometimes you may want to delete these dependent rows when you delete the row they depend on. In the case of BOOKSHELF and CATEGORY, if you delete a CATEGORY_NAME from CATEGORY, you may want to make the matching BOOKSHELF CATEGORY_NAME column values NULL. In another case, you might want to delete the whole row. The clause ON DELETE CASCADE added to the REFERENCES clause tells Oracle to delete the dependent row when you delete the corresponding row in the parent table. This action automatically maintains referential integrity.
The CHECK Constraint
Many columns must have values that are within a certain range or that satisfy certain conditions. With a CHECK constraint, you can specify an expression that must always be true for every row in the table. For example, the RATING table stores valid ratings; to limit the available values beyond the limits enforced by the column definition, you can use a CHECK constraint, as shown in the following listing:
A column-level CHECK constraint can’t refer to values in other rows; it can’t use the pseudo-columns such as SYSDATE, USER, CURRVAL, NEXTVAL, LEVEL, and ROWNUM. You can use the table constraint form (as opposed to the column constraint form) to refer to multiple columns in a CHECK constraint.
Naming Constraints
You can name your constraints. If you use an effective naming scheme for your constraint names, you will be better able to identify and manage the constraints. The name of a constraint should identify the table it acts on and the type of constraint it represents. For example, the primary key on the TROUBLE table could be named TROUBLE_PK.
You can specify a name for a constraint when you create the constraint. If you do not specify a name for the constraint, Oracle generates a name. Most of Oracle’s generated constraint names are of the form SYS_C###### (for example, SYS_C000145). Because the system-generated constraint name does not tell you anything about the table or the constraint, you should name your constraints.
In the following example, the PRIMARY KEY constraint is created and named, as part of the CREATE TABLE command for the TROUBLE table (notice the CONSTRAINT clause):
The CONSTRAINT clause of the CREATE TABLE command names the constraint (in this case, TROUBLE_PK). You may use this constraint name later when enabling or disabling constraints.