Add primary keys and indexes in Oracle

The primary key and index of data are usually required. Especially when a table has a large amount of data, the index and primary key are even more necessary, which can provide data query efficiency;

I. Create a primary key constraint while creating a table

(1) No Name

Create table student (

Studentid int primary key not null,

Studentname varchar (8 ),

Age int );

(2) named

Create table students (

Studentid int,

Studentname varchar (8 ),

Age int,

Constraint yy primary key (studentid ));

2. Delete the existing primary key constraints in the table

(1) named

Alter table students drop constraint yy;

(2) No Name

SELECT * from user_cons_columns;

Find the primary key name in the student table. The primary key name is SYS_C002715.

Alter table student drop constraint SYS_C002715;

3. Add a primary key constraint to the table

Alter table student add constraint pk_student primary key (studentid );

3. When creating a primary key, the original primary key column contains data with duplicates. In this case, you must delete the duplicate data before creating it:

Delete from Tb_TEST a where rowid! = (Select max (rowid) from Tb_TEst B where a. test_ID = B. test_ID );

2. Create an index

Create Index IX_xxxx on table_name (colummname );