Unique and index in Oracle 10 GB

Unique and index in Oracle 10 GB

Unique and index in Oracle 10 GB

Create table test

(

X integer,

Y integer,

Z integer

);

Alter table test add constraint primary key (x); -- generates a constraint and an index of the same name.

Create unique index index01 on test (x, y );

-- Create unique index index02 on test (x, y); -- identical indexes are not allowed, even if the index names are different.

Create unique index index03 on test (y, z );

Create unique index index04 on test (z, y); -- index04 is an index different from index03

Alter table test add constraint cons01 unique (x, y, z); -- generates a constraint and automatically generates a UNIQUE index named cons01

Alter table test add constraint cons01 unique (x, y

Alter table test add constraint cons01 unique (y, x); -- generates a constraint, but because it is the same as index01, indexes cannot be automatically generated.

-- Create unique index cons01 on test (x, y); -- cannot be executed because the index cons01 has been bound to the index automatically created by cons01

Alter table test add constraint cons012 check (x> 100 );
Alter table test add constraint cons013 check (x> 100); -- check can have the same content

Solution:

Extract all constraints (UCPR)

Extract all indexes whose names are not in the restricted table