Description of the relationship between the unique constraint (unique constraint) and index in Oracle

 

1. Description of unique constraints on the official website

Http://download.oracle.com/docs/cd/E11882_01/server.112/e16508/datainte.htm#CNCPT1642

 

Uniquekey constraint requires that every value in a column or set of columns beunique. no rows of a table may have duplicate values in a column (the uniquekey) or set of columns (the composite unique key) with a unique key constraint.

 

Note:

Theterm key refers only to the columns defined in the Integrity Constraint. because the database enforces a unique constraint byimplicitly creating or reusing an index on the key columns, the term uniquekey is sometimes incorrectly used as a synonym for unique key constraint orunique index.

-- When creating a unique constraint, the database forces the creation or reuse of column indexes. If no index exists in the previous column, the created index is unique index. If an index already exists in the column, the previous index will be reused.

 

Uniquekey constraints are appropriate for any column where duplicate values are notallowed. unique constraints differ from primary keyconstraints, whose purpose is to identify each table row uniquely, andtypically contain values that have no significance other than being unique. examples of unique keys include:

(1) A customer phone number, where the primary key is the customernumber

(2) A department name, where the primary key is the department number

 

Asshown in Example2-1, a unique key constraint exists on the email column of the HR. employeestable. The relevant part of the statement is as follows:

Create Table employees (...

, Email varchar2 (25)

Constraint emp_email_nn not null...

, Constraint emp_email_uk unique (email )...);

 

Theemp_email_uk constraint ensures that no two employees have the same emailaddress, as shown in Example5-1.

 

Example 5-1 unique constraint

SQL> select employee_id, last_name, email from employees where email = 'pfay ';

 

Employee_id last_name email

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

202 Fay pfay

 

SQL> insert into employees (employee_id, last_name, email, hire_date, job_id)

1 values (999, 'fay', 'pfay', sysdate, 'st _ cler ');

.

.

.

Error at line 1:

ORA-00001: unique constraint (HR. emp_email_uk) violated

 

Unless a not nullconstraint is also defined, a null always satisfies a unique key constraint. thus, columns with both unique key constraints and not null constraints are typical. this combination forces the user to enter values in the unique key andeliminates the possibility that new row data conflicts with existing row data.

 

Note:

Because of the searchmechanic for unique key constraints on multiple columns, you cannot haveidentical values in the non-null columns of a partially null composite uniquekey constraint.

 

 

Ii. related tests 2.1 testing unique index and uniqueconstraint

Sys @ anqing2 (rac2)> Create Table Ut (idnumber, phone varchar2 (15), name varchar2 (15 ));

Table created.

 

Sys @ anqing2 (rac2)> insert into utvalues (1, '20140901', 'Dave ');

1 row created.

Sys @ anqing2 (rac2)> insert into utvalues (1, '20140901', 'Dave ');

1 row created.

Sys @ anqing2 (rac2)> insert into utvalues (2, '20140901', 'dba ');

1 row created.

Sys @ anqing2 (rac2)> commit;

Commit complete.

 

-- On the phone field, we create uniqueconstraint

Sys @ anqing2 (rac2)> alter table ut addconstraint uc_phone unique (phone );

Alter table ut add constraint uc_phoneunique (phone)

*

Error at line 1:

ORA-02299: cannot validate (SYS. uc_phone)-duplicate keys found

-- An error is reported here, because we have duplicate values when inserting data. delete them first.

 

Sys @ anqing2 (rac2)> select * From ut;

 

ID phone name

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

1 13888888888 Dave

2 13899999999 DBA

1 13888888888 Dave

 

Sys @ anqing2 (rac2)> Delete from ut whererownum = 1;

1 row deleted.

 

Sys @ anqing2 (rac2)> commit;

Commit complete.

 

Sys @ anqing2 (rac2)> select * From ut;

ID phone name

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

2 13899999999 DBA

1 13888888888 Dave

 

-- The uniqueness constraint is successfully created.

Sys @ anqing2 (rac2)> alter table ut addconstraint uc_phone unique (phone );

Table altered.

 

-- View Constraints

Sys @ anqing2 (rac2)> selectconstraint_name, constraint_type, table_name, index_owner, index_name fromuser_constraints where table_name = 'ut ';

 

Constraint_name C table_name index_owner index_name

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

Uc_phone U ut sys uc_phone

-- Oracle automatically creates an index and associates it with a constraint. The index name and constraint name are the same.

 

-- Verify the index

Sys @ anqing2 (rac2)> selectindex_name, index_type, uniqueness, generated from user_indexes wheretable_name = 'ut ';

 

Index_name index_type uniquenes generated

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

Uc_phone normal unique n

-- We didn't create an index, but when creating a unique constraint, Oracle forcibly created a uniqueindex.

 

-- Now let's take a look at the drop index.

Sys @ anqing2 (rac2)> drop index uc_phone;

Drop index uc_phone

*

Error at line 1:

ORA-02429: cannot drop index used forenforcement of unique/primary key

-- An error is returned. Indexes on the unique/primary key cannot be deleted. In this case, we only need to delete the constraint first.

 

Sys @ anqing2 (rac2)> alter table ut dropconstraint uc_phone;

Table altered.

 

Sys @ anqing2 (rac2)> drop index uc_phone;

Drop index uc_phone

*

Error at line 1:

ORA-01418: specified index does not exist

-- When you drop the index again, the system prompts that the index does not exist, indicating that the index has been deleted while deleting the constraint.

 

Sys @ anqing2 (rac2)> selectconstraint_name, constraint_type, table_name, index_owner, index_name fromuser_constraints where table_name = 'ut ';

No rows selected

 

Sys @ anqing2 (rac2)> selectindex_name, index_type, uniqueness, generated from user_indexes wheretable_name = 'ut ';

No rows selected

 

Conclusion:

When the constraint column does not have an index, Oracle automatically creates a unique index when creating a unique constraint, and the index cannot be deleted. When unique constraint is deleted, the unique index is automatically deleted.

 

 

2.2 test unique constraint and non-unique index

 

-- Create a B-tree index on the phone Field

Sys @ anqing2 (rac2)> Create indexidx_ut_phone on Ut (phone );

Index created.

 

-- View Indexes

Sys @ anqing2 (rac2)> selectindex_name, index_type, uniqueness, generated from user_indexes wheretable_name = 'ut ';

Index_name index_type uniquenes generated

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

Idx_ut_phone normal nonunique n

 

-- Create unique constraint

Sys @ anqing2 (rac2)> alter table ut add constraint uc_phoneunique (phone );

Table altered.

 

-- View constraints and index information

Sys @ anqing2 (rac2)> selectconstraint_name, constraint_type, table_name, index_owner, index_name fromuser_constraints where table_name = 'ut ';

 

Constraint_name C table_name index_owner index_name

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

Uc_phone U ut sys idx_ut_phone

-- The existing index is reused here.

 

Sys @ anqing2 (rac2)> selectindex_name, index_type, uniqueness, generated from user_indexes wheretable_name = 'ut ';

Index_name index_type uniquenes generated

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

Idx_ut_phone normal nonunique n

 

-- Delete an index

Sys @ anqing2 (rac2)> drop indexidx_ut_phone;

Drop index idx_ut_phone

*

Error at line 1:

ORA-02429: cannot drop index used forenforcement of unique/primary key

-- This prompt is the same as before. Let's Delete the constraint first and check it later.

 

Sys @ anqing2 (rac2)> alter table ut dropconstraint uc_phone;

Table altered.

 

Sys @ anqing2 (rac2)> select constraint_name, constraint_type, table_name, index_owner, index_namefrom user_constraints where table_name = 'ut ';

No rows selected

-- The constraint has been deleted.

 

Sys @ anqing2 (rac2)> selectindex_name, index_type, uniqueness, generated from user_indexes wheretable_name = 'ut ';

Index_name index_type uniquenes generated

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

Idx_ut_phone normal nonunique n

-- But our index is deleted when the constraint is deleted.

 

-- The index is deleted manually.

Sys @ anqing2 (rac2)> drop indexidx_ut_phone;

Index dropped.

 

Sys @ anqing2 (rac2)> selectindex_name, index_type, uniqueness, generated from user_indexes wheretable_name = 'ut ';

No rows selected

 

-- Add constraints and indexes again, and then delete them once.

Sys @ anqing2 (rac2)> Create indexidx_ut_phone on Ut (phone );

Index created.

Sys @ anqing2 (rac2)> alter table ut addconstraint uc_phone unique (phone );

Table altered.

 

Sys @ anqing2 (rac2)> selectconstraint_name, constraint_type, table_name, index_owner, index_name fromuser_constraints where table_name = 'ut ';

Constraint_name C table_name index_owner index_name

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

Uc_phone U ut sys idx_ut_phone

 

Sys @ anqing2 (rac2)> selectindex_name, index_type, uniqueness, generated from user_indexes wheretable_name = 'ut ';

Index_name index_type uniquenes generated

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

Idx_ut_phone normal nonunique n

 

Sys @ anqing2 (rac2)> alter table ut drop constraint uc_phone drop index;

Table altered.

Sys @ anqing2 (rac2)> selectconstraint_name, constraint_type, table_name, index_owner, index_name fromuser_constraints where table_name = 'ut ';

No rows selected

Sys @ anqing2 (rac2)> selectindex_name, index_type, uniqueness, generated from user_indexes wheretable_name = 'ut ';

No rows selected

-- One-time deletion of indexes and constraints

 

 

Summary:

When we create a unique constraint for an index on a column, Oracle will reuse the previous index without changing the index type. In the first test, the Index automatically created by Oracle is unique index.

When we delete the constraint, the associated index is not automatically deleted. The MOs of this problem is described. Refer to Mos [id309821.1].

We can delete the constraint in two steps before deleting the index. Mos provides a method to add drop index when deleting the constraint, so that you can get it done once.

SQL> altertable ut drop constraint uc_phone drop index;

 

 

 

 

Bytes -------------------------------------------------------------------------------------------------------

Blog: http://blog.csdn.net/tianlesoftware

Email: dvd.dba@gmail.com

Dba1 group: 62697716 (full); dba2 group: 62697977 (full) dba3 group: 62697850 (full)

Super DBA group: 63306533 (full); dba4 group: 83829929 dba5 group: 142216823

Dba6 group: 158654907 chat group: 40132017 chat group 2: 69087192

-- Add the group to describe the relationship between Oracle tablespace and data files in the remarks section. Otherwise, the application is rejected.