In general, most database tables require a primary index. (Some tables in the Data Dictionary do not have primary indexes and a global temporary trace table is not allowed to have a primary index.) To create a primary index, use the CREATE TABLE statement.
If you do not assign a primary index explicitly when you create a table, the database assigns a primary index, based on the following rules.
| Primary Index | Primary Key | Unique Column Constraint | The database selects the … |
|---|---|---|---|
| no | YES | no | primary key column set to be a UPI. |
| no | no | YES | first column or columns having a UNIQUE constraint to be a UPI. |
| no | YES | YES | primary key column set to be a UPI. |
| no | no | no | first column defined for the table to be a NUPI. If the data type of the first column in the table is a LOB, then the CREATE TABLE operation aborts and the system returns an error message. |
In general, the best practice is to specify a primary index instead of having the database select a default primary index.