Primary Keys Wizard (Oracle)

From DBArtisan
Jump to: navigation, search

Go Up to Oracle Object Wizards

Primary key (constraint)s are a set of table columns that can uniquely identify every row of a table. No fields that are a part of the primary key can have null values, and each table can have only one primary key.

To create a new primary key using a wizard:

  1. Open a creation wizard for a primary 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:
  3. Finally, use the Execute button to create the object.

Primary Keys (Oracle) - Properties

When creating or editing a primary 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 primary key is being created.


Provide a name for the primary 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.


Enabling logs this operation to the redo file.


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.


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 cheeked against the constraint.


Dictates whether constraint checking can be deferred until the end of a transaction.


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.


Enables or disables the primary key.

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

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

Primary Keys (Oracle) - Partition

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