Managing Constraints   «Prev  Next»

Lesson 4 UNIQUE constraints
ObjectiveCreate UNIQUE constraints on database tables.

UNIQUE Key Constraints on Tables

In the previous lesson, you learned to create a primary key for a table. One of the attributes of a PRIMARY KEY is that the value for a primary key must be unique. You also can force a column to be unique by using the UNIQUE constraint.
The UNIQUE constraint requires the column to contain a unique value for every row in the table. This constraint only applies to rows that actually have values. If a row contains a NULL value for a column, it satisfies a UNIQUE constraint, even if there are other rows in the table with NULL values. The reason for this is simple, namely a NULL value is not equal to anything, so the condition NULL = NULL always evaluates to FALSE.
Therefore, a NULL value in a unique column is still not equivalent to any other value in any other row in the table, and one or more NULL values does not violate a UNIQUE constraint.
Oracle uses indexes to enforce UNIQUE constraints, as well as PRIMARY KEY constraints, which also require unique values. If a unique index on a column that is the subject of a UNIQUE constraint already exists, the index is used to enforce the constraint. If there is no appropriate index, Oracle creates one.

Example of UNIQUE Key Constraints

There are no UNIQUE constraints required in the COIN database, but if you want only a single client row for each client email address, you can define a unique client_email column by using the following syntax. View the code below to see it. 
CREATE TABLE CLIENT (client_id NUMBER, client_name VARCHAR2(20), 
client_phone VARCHAR2(14), client_email VARCHAR2(40), 
client_password VARCHAR2(8), 
CONSTRAINT unique_client_email UNIQUE (client_email);

If you want to specify that Oracle use an existing index (idx_email) to enforce a constraint, you can define the constraint by using the ALTER TABLE syntax.
View the code below. 
ALTER TABLE ADD CONSTRAINT unique_email 
UNIQUE (client_email) USING idx_email;

Creating a unique index with Schema Manager

  1. Select the Schema Manager from the Oracle Enterprise Manager program group. Start Schema Manager by selecting the program from the Oracle Enterprise Manager group.
  2. Login in as user SCOTT with password TIGER and then click the OK button.
  3. The Schema Manager gives you a graphical view of all the database objects. To see a listing of indexes, click the plus sign to the left of the Indexes heading in the list box on the left.
  4. The Indexes listing contains all the schemas that the user SCOTT has access to. Click the plus sign to the left of the SCOTT schema to show the Indexes in that schema.
  5. To see the details of the DEPT_PRIMARY_KEY index, click the index name in the left list box.
  6. To create a new index, right click the SCOTT schema name.
  7. Select the Create menu choice.
  8. The Create Index form will prompt you for an index name. Enter A_UNIQUE_INDEX in the Name box.
  9. By default, the Create Index form starts with the first table in the schema in alphabetical order. This is the A_TABLE that you created in an earlier module, and is the one you want to use.
  10. The index also will contain the one column in the table, COLUMN1. To specify this column as part of the index, click the box to the left of the column name in the column list box.
  11. Once you select a column, it is given an order in the index; in this case, 1.
  12. You have to indicate that this is a unique index by selecting the Unique check box near the bottom of the Create Index form.
  13. The final step in creating the index is to click the Create push button at the bottom of the page.
  14. When you return to the Schema Manager, you can see that the A_UNIQUE_INDEX entry is now in the list of indexes. This is the end of the simulation.

Figure 64-6 SchemaManager Usage
Figure 64-6 SchemaManager Usage
Toplink Links
Although you can use the SchemaManager API directly, Oracle recommends that you create a TableCreator class and use its API (which, in turn, uses the SchemaManager). The TableCreator class owns one or more TableDefinition classes (one for each database table) and the TableDefinition class owns one or more FieldDefinition classes (one for each field). The TableDefinition class lets you specify a database table schema in a generic format. At run time, TopLink uses the session associated with your TopLink project to determine the specific database type, and uses the generic schema to create the appropriate tables and fields for that database.


Oracle Constraints

A constraint enforces certain aspects of data integrity within a database. When you add a constraint to a particular column, Oracle automatically ensures that data violating that constraint is never accepted. If a user attempts to write data that violates a constraint, Oracle returns an error for the offending SQL statement
Constraints may be associated with columns when you create or add the table containing the column (via a number of keywords) or after the table has been created with the SQL command ALTER TABLE. Since Oracle8, the following constraint types are supported:

Unique Columns

When you designate a column or set of columns as unique, users cannot add values that already exist in another row in the table for those columns, or modify existing values to match other values in the column. The unique constraint is implemented by the creation of an index, which requires a unique value. If you include more than one column as part of a unique key, you will create a single index that will include all the columns in the unique key. If an index already exists for this purpose, Oracle will automatically use that index.
The next lesson explains the FOREIGN KEY constraint.