Unique and Primary Keys

A unique constraint designates a column as a unique key. A composite unique key designates a combination of columns as the unique key.

To satisfy a unique constraint, no two rows in the table can have the same value for the unique key. However, the unique key made up of a single column can contain Nulls. To satisfy a composite unique key, no two rows in the table or view can have the same combination of values in the key columns. Any row that contains Nulls in all key columns automatically satisfies the constraint. However, two rows that contain Nulls for one or more key columns and the same combination of values for the other key columns violate the constraint.

A primary key constraint designates a column as the primary key of a table or view. A composite primary key designates a combination of columns as the primary key. A primary key combines a Not Null and unique constraint in one declaration. Therefore, to satisfy a primary key constraint no primary key value can appear in more than one row in the table, and no column that is part of the primary key can contain a Null.

Certain restrictions must be obeyed when working with unique and primary keys:

  • A table or view can have only one primary key.
  • A composite key cannot have more than 32 columns.
  • You cannot designate the same column or combination of columns as both a primary key and a unique key.
  • Some column types such as LOB, LONG, VARRAY, NESTED TABLE, REF are not supported for unique or primary keys.

To Create a Key

Open the table you need and choose New Primary Key or New Unique Key from Table menu. Add required columns to the key and click OK. You can also switch to Constraints tab and create a key within the shortcut menu.

To Edit a Key

Navigate to the key in Database Explorer and double-click it or open the table that owns the key, switch to Constraints tab and click Edit Constraint on the shortcut menu.

To Drop a Key

Navigate to the key in Database Explorer and choose Delete from the shortcut menu or open the table that owns the key, switch to Constraints tab and choose Delete Constraint from the key shortcut menu.