Unique Keys Wizard (Oracle)

Go Up to Oracle Object Wizards

A unique key constraint requires that every value in a column or set of columns be unique. Thus, no two rows of a table have duplicate values in the column or set of columns you identified. So, for example, you can use a unique key constraint to make sure you haven’t duplicated a social security number in a list of employees.

To create a new unique key using a wizard:

1. Open a creation wizard for a unique key. For details, see Opening an Object Wizard.
2. Use the following topics as a guide to setting properties and performing tasks as you pass through the wizard panels:
   • Properties panel - for details, see Unique Keys (Oracle) - Properties.
   • Columns panel - for details, see Unique Keys (Oracle) - Columns.
   • Storage panel - for details, see Unique Keys (Oracle) - Storage.
   • Partition panel - for details, see Unique Keys (Oracle) - Partition.
   • DDL panel - for details, see Previewing the DDL Generated to Create the New Object.
3. Finally, use the Execute button to create the object.

Unique Keys (Oracle) - Properties

When creating or editing a unique key, this tab/panel lets you work with the following settings:

Setting	Description
Table Owner and Table Name	Choose the owner and name of the table in which the unique key is being created.
Name	Provide a name for the unique key being created.
No Sort	Enable this feature if the rows in the table already stored in ascending order. This increases the speed of the index creation process. Oracle does not sort the rows.
Logging	Enabling logs this operation to the redo file.
Reverse	Enabling this feature stores the bytes of the index block in reverse order and excludes the ROWID. The ROWID is a globally unique identifier for a row in a database. It is created at the time the row is inserted into a table, and destroyed when it is removed from a table.
Validate	Enabling this option indicates that existing data is checked against the constraint when the primary key is enabled. Leaving it disabled indicates that only new data is to be checked against the constraint.
Deferrable	Dictates whether constraint checking can be deferred until the end of a transaction.
Deferred	This option is enabled only if you enabled the Deferrable option. Select IMMEDIATE to have the constraint checked at the end of every DDL statement. Select DEFERRED to have the constraint checked only at the end of a transaction.
Enabled	Enables or disables the primary key.

Unique Keys (Oracle) - Columns

From the Column dropdown, select a column for the index and specify a Sort option. To add more columns, click the New button and then follow the steps in the last instruction. Use the Delete button to drop columns.

Unique Keys (Oracle) - Storage

When creating or editing a primary key, this tab/panel lets you work with the following settings:

Setting	Description
Data Block Storage group	Select the DEFAULT Tablespace only if you are creating a local partitioned index and want the partitions in the same tablespace as the partitions in the underlying table. (Each partition of a local index is associated with one partition of the table. Oracle can then keep the index partitions in synch with table partitions.) A transaction entry is needed for each INSERT, UPDATE, DELETE, etc. statement that accesses one or more rows in the block. Transaction entries in many operating systems require approximately 23 bytes. Percent Free identifies how much space you want to allocate for new rows or updates to existing rows. Initial Transactions ensures that a minimum number of concurrent transactions can update an index block, avoiding the overhead of allocating a transaction entry dynamically. Maximum Transactions limits concurrency on an index block.
Extents group	An extent is the unit of space allocated to an object whenever the object needs more space. Initial Extent - The initial space extent (in bytes) allocated to the object. Next Extent - The next extent (in bytes) that the object will attempt to allocate when more space for the object is required. Percentage Increase - Lets you type the percentage. NOTE: You should be careful when setting Percent Increase because it magnifies how an object grows and, therefore, can materially affect available free space in a tablespace. Minimum Extents - For a dictionary managed tablespace, this is the total number of extents to be allocated when the index is first created. For a locally managed tablespace, this is simply the initial amount of space allocated. Maximum Extents - For a dictionary managed tablespace, this is the total number of extents that can ever be allocated to the index. In a locally managed tablespace, the database will automatically manage the extents.
Freelists group	Free lists let you manage the allocation of data blocks when concurrent processes are issued against the index. You can potentially improve the performance of the index by identifying multiple free lists, which can reduce contention for free lists when concurrent inserts take place. The default and minimum value is 1. You should increase this number if multiple processes access the same data block. Free List Groups is the number of groups of free lists. NOTE: This option is only applicable for the parallel server option.
Buffer Pool	DEFAULT - Choose this if you want to use the default bufferpool. KEEP - Use this to retain the object in memory to avoid I/O conflicts. This type of bufferpool stores frequently referenced data blocks in a separate cache. RECYCLE - Select this option to save cache space by ridding data blocks from memory as soon as they are no longer in use.

Unique Keys (Oracle) - Partition

Clicking Create Partition opens a wizard that lets you create a partition.