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.
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.
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:
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.