Differences between Oracle primary keys, unique keys, and unique Indexes

Generally, we can see that the terms "Index" and "key" are used for exchange, but they are actually different. An index is a physical structure stored in a database. A key is purely a logical concept. The key represents the integrity constraints created to implement business rules. The confusion between indexes and keys is generally caused by the use of indexes in the database to implement integrity constraints.

Recommended reading:

Automatic Indexing of Oracle primary key constraints

Simulation and Analysis on the unique constraint index cannot be deleted after Oracle deletes the primary key constraint for 10 GB

Introduction to Oracle Virtual Index

Check whether indexes need to be reconstructed in Oracle

Oracle case when index null index bitmap Index

Oracle analysis table and Index

Automatic Indexing of Oracle primary key constraints

 
Next, let's take a look at the differences between primary key constraints, unique key constraints, and unique indexes in the database.
SQL> select * from v $ version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-Production
PL/SQL Release 11.2.0.1.0-Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0-Production
NLSRTL Version 11.2.0.1.0-Production
 
SQL> create table test (
2 id int,
3 name varchar2 (20 ),
4 constraint pk_test primary key (id ))
5 tablespace users;
Table created.
 
SQL> select constraint_name, constraint_type from user_constraints;
CONSTRAINT_NAME C
-------------------------------
PK_TEST P
 
In the test table, we specify the ID column as the primary key. The Oracle database automatically creates a unique index with the same name:
SQL> select index_name, index_type, uniqueness, tablespace_name
2 from user_indexes
3 where table_owner = 'Scott'
4 and table_name = 'test ';
INDEX_NAME INDEX_TYPE UNIQUENES TABLESPACE_NAME
-------------------------------------------------------------------------------
PK_TEST NORMAL UNIQUE USERS
 
In this case, if we try to create a unique index on the ID column, Oracle will report an error because the column already has a unique index:
SQL> create unique index idx_test_uk on test (id );
Create unique index idx_test_uk on test (id)
*
ERROR at line 1:
ORA-01408: such column list already indexed
 
Not even when creating a non-unique index:
SQL> create index idx_test_id on test (id );
Create index idx_test_id on test (id)
*
ERROR at line 1:
ORA-01408: such column list already indexed
 
What is the unique key constraint?
SQL> drop table test purge;
Table dropped.
 
SQL> create table test (
2 id int,
3 name varchar2 (20 ),
4 constraint uk_test unique (id ));
Table created.
 
SQL> select constraint_name, constraint_type from user_constraints;
CONSTRAINT_NAME C
-------------------------------
UK_TEST U
 
View the index information at this time:
SQL> select index_name, index_type, uniqueness, tablespace_name
2 from user_indexes
3 where table_owner = 'Scott'
4 and table_name = 'test ';
INDEX_NAME INDEX_TYPE UNIQUENES TABLESPACE_NAME
-------------------------------------------------------------------------------
UK_TEST NORMAL UNIQUE USERS
Oracle also automatically creates a unique index with the same name, and it is not allowed to create a unique or non-unique index on this column.
 
We know that the primary key constraint requires that the column value be not null. Do the unique key constraint also require non-NULL values?
SQL> insert into test values (1, 'Sally ');
1 row created.
 
SQL> insert into test values (null, 'Tony ');
1 row created.
 
SQL> insert into test values (null, 'jack ');
1 row created.
 
SQL> select * from test;
ID NAME
------------------------------
1 Sally
Tony
Jack
 
From the experiment results, the unique key constraint is not required.
 
Next, let's take a look at the differences between unique indexes and non-null column values.
 
SQL> drop table test purge;
Table dropped.
 
SQL> create table test (
2 id int,
3 name varchar2 (20 ));
Table created.
 
SQL> create unique index idx_test_id on test (id );
Index created.
 
SQL> insert into test values (1, 'Sally ');
1 row created.
 
SQL> insert into test values (null, 'Tony ');
1 row created.
 
SQL> insert into test values (null, 'jack ');
1 row created.
 
SQL> select * from test;
ID NAME
------------------------------
1 Sally
Tony
Jack
 
From the experiment, we can see that the unique index is not required for non-null column values just like the unique key constraint.

 

To continue reading the highlights of this article, please refer to page 2nd:

1 2 3 Next Page