Differences between Oracle primary key constraint, unique key constraint, and unique index [primary key constraint and unique key

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. Next let's look at the number

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. Next let's look at the number

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.

Next, let's take a look at the differences between primary key constraints, unique key constraints, and unique indexes in the database.

SQL> select * fromv $ version;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Release iseedition 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 tabletest (

2Id int,

3Namevarchar2 (20 ),

4Constraintpk_testprimarykey (id ))

5Tablespaceusers;

Table created.

SQL> selectconstraint_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> selectindex_name, index_type, uniqueness, tablespace_name

2Fromuser_indexes

3Wheretable_owner = 'Scott'

4And 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 uniqueindex idx_test_uk on test (id );

Create unique index idx_test_uk ontest (id)

*

ERROR at line 1:

ORA-01408: such column list alreadyindexed

Not even when creating a non-unique index:

SQL> create indexidx_test_id on test (id );

Create index idx_test_id ontest (id)

*

ERROR at line 1:

ORA-01408: such column list alreadyindexed

What is the unique key constraint?

SQL> drop table testpurge;

Table dropped.

SQL> create tabletest (

2Id int,

3Namevarchar2 (20 ),

4Constraintuk_testunique (id ));

Table created.

SQL> selectconstraint_name, constraint_type from user_constraints;

CONSTRAINT_NAME C

-------------------------------

UK_TEST U

View the index information at this time:

SQL> selectindex_name, index_type, uniqueness, tablespace_name

2Fromuser_indexes

3Wheretable_owner = 'Scott'

4And 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 * fromtest;

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 testpurge;

Table dropped.

SQL> create tabletest (

2Id int,

3Namevarchar2 (20 ));

Table created.

SQL> create uniqueindex 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 * fromtest;

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.

If we invalidate the primary key constraint or unique key constraint, will the unique Index automatically created by Oracle be affected?

SQL> drop table testpurge;

Table dropped.

SQL> create tabletest (

2Id int,

3Namevarchar2 (20 ),

4Constraint uk_testunique (id ));

Table created.

SQL> selectindex_name, index_type, uniqueness from user_indexes;

INDEX_NAME INDEX_TYPE UNIQUENES

------------------------------------------------------------------

UK_TEST NORMAL UNIQUE

SQL> alter tabletest disable constraint uk_test;

Table altered.

SQL> selectindex_name, index_type, uniqueness from user_indexes;

No rows selected

If the primary key constraint or unique key constraint is invalid, Oracle deletes the unique index created implicitly.

What if we first create a unique index and then create a primary key or unique key constraint?

SQL> drop table testpurge;

Table dropped.

SQL> create tabletest (

2Id int,

3Namevarchar2 (20 ));

Table created.

SQL> create uniqueindex idx_test_id on test (id );

Index created.

SQL> selectindex_name, index_type, uniqueness

2Fromuser_indexes

3Where table_owner = 'Scott'

4And table_name = 'test ';

INDEX_NAME INDEX_TYPE UNIQUENES

------------------------------------------------------------------

IDX_TEST_ID NORMAL UNIQUE

SQL> alter tabletest add constraint uk_test unique (id );

Table altered.

SQL> selectindex_name, index_type, uniqueness

2Fromuser_indexes

3Where table_owner = 'Scott'

4And table_name = 'test ';

INDEX_NAME INDEX_TYPE UNIQUENES

------------------------------------------------------------------

IDX_TEST_ID NORMAL UNIQUE

SQL> selectconstraint_name, constraint_type

2Fromuser_constraints

3Where table_name = 'test ';

CONSTRAINT_NAME C

-------------------------------

UK_TEST U

SQL> alter tabletest disable constraint uk_test;

Table altered.

SQL> selectconstraint_name, constraint_type, status

2Fromuser_constraints

3Where table_name = 'test ';

CONSTRAINT_NAME C STATUS

---------------------------------------

UK_TEST U DISABLED

SQL> selectindex_name, index_type, uniqueness, status

2Fromuser_indexes

3Where table_owner = 'Scott'

4And table_name = 'test ';

INDEX_NAME INDEX_TYPE UNIQUENES STATUS

--------------------------------------------------------------------------

IDX_TEST_ID NORMAL UNIQUE VALID

The experimental results show that the unique index created first is not affected by the constraint failure.

Summary:

(1) Both primary key constraints and unique key constraints implicitly create a unique index with the same name. If the primary key constraint or unique key constraint is invalid, the unique index created by implicit creation will be deleted;

(2) The primary key constraint requires that the column value be not empty, while the unique key constraint and the unique index do not require the column value to be non-empty;

(3) Duplicate index creation is not allowed for the same field sequence;