cherioss cherioss - 7 months ago 16
SQL Question

create index, Oracle

I have Oracle database. I want to create INDEX:

CREATE INDEX indexID ON Employee(id_employee);


But it writes ->
SQL Error: ORA-01408: such column list already indexed


So before create index I put:

DROP INDEX indexID;


But it writes ->
SQL Error: ORA-01418: specified index does not exist


my Employee table:

CREATE TABLE Employee (
id_employee NUMBER(5) NOT NULL,
name VARCHAR(25) NOT NULL,
surname VARCHAR(25) NOT NULL,
day_of_birth DATE NOT NULL,
salary NUMBER(6) NOT NULL,

PRIMARY KEY(id_employee)
);


Have you some idea? it looks like index does not create.

Answer

You have a different index on that column. Say you create the table like this:

SQL> CREATE TABLE Employee (
  2    id_employee NUMBER(5) NOT NULL ,
  3    name VARCHAR(25) NOT NULL,
  4    surname VARCHAR(25) NOT NULL,
  5    day_of_birth DATE NOT NULL,
  6    salary NUMBER(6) NOT NULL
  7  );

Table created.

Then you add the PK constraint:

SQL> alter table employee add primary key(id_employee);

Table altered.

Now Oracle already created a unique index on the PK field, so you already have it, with no need for manual creation.

SQL> select index_name, column_name
  2  from user_ind_columns c
  3          inner join user_indexes i
  4           using (index_name)
  5  where i.table_name = 'EMPLOYEE';

INDEX_NAME           COLUMN_NAME
-------------------- --------------------
SYS_C007892          ID_EMPLOYEE

In your example:

SQL> CREATE TABLE Zamestnanec (
  2    id_zamestnance NUMBER(5) PRIMARY KEY ,
  3    jmeno VARCHAR(25) NOT NULL,
  4    prijmeni VARCHAR(25) NOT NULL,
  5    datum_narozeni DATE NOT NULL,
  6    prava CHAR(3) CHECK(prava IN ('ano', 'ne')) NOT NULL,
  7    plat NUMBER(6) NOT NULL
  8  );

Table created.

SQL> select index_name, column_name
  2  from user_ind_columns c
  3          inner join user_indexes i
  4           using (index_name)
  5  where i.table_name = 'ZAMESTNANEC';

INDEX_NAME           COLUMN_NAME
-------------------- --------------------
SYS_C007899          ID_ZAMESTNANCE

I slightly modified your syntax; besides, you can avoid the NOT NULL constraint on a PK field: the PK will force the field to be NOT NULL.

See here for a similar problem.