Indexes for the PRIMARY KEY Column in Oracle

Q

What Is an Index Associated with a Constraint in Oracle?

✍: FYIcenter.com

A

An index associated with a constraint because this constraint is required to have an index. There are two types of constraints are required to have indexes: UNIQUE and PRIMARY KEY. When you defines a UNIQUE or PRIMARY KEY constraint in a table, Oracle will automatically create an index for that constraint. The following script shows you an example:

CREATE TABLE student (id NUMBER(5) PRIMARY KEY,
  first_name VARCHAR(80) NOT NULL,
  last_name VARCHAR(80) NOT NULL,
  birth_date DATE NOT NULL,
  social_number VARCHAR(80) UNIQUE NOT NULL);
Table created.
  
SELECT index_name, table_name, uniqueness 
  FROM USER_INDEXES WHERE table_name = 'STUDENT';
INDEX_NAME              TABLE_NAME            UNIQUENES
----------------------- --------------------- ---------
SYS_C004123             STUDENT               UNIQUE
SYS_C004124             STUDENT               UNIQUE

The result confirms that Oracle automatically created two indexes for you.

 

Rename an Index in Oracle

Show All Indexes in Your Schema in Oracle

Managing Oracle Table Indexes

⇑⇑ Oracle Database Tutorials

2019-05-01, 1829🔥, 0💬