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;