ORA-00911: invalid character on oracle, but works with H2

Question

I have the following SQL command, it works with H2 database, but when i try to run it on Oracle XE, it gets the " ORA-00911: invalid character " error.

create table EMPLOYEE (
EMPLOYEE_KEY       NUMBER(10) not null,
SALARY             NUMBER(10,2),
LAST_NAME          VARCHAR2(132),
FIRST_NAME         VARCHAR2(132),
SUPERVISOR_KEY     NUMBER(10),
constraint EMPLOYEE_PK primary key (EMPLOYEE_KEY)
);
create unique index EMPLOYEE_PK on EMPLOYEE(EMPLOYEE_KEY);

Answer 1

With this example, when you created the primary key constraint, you also created an index with the same name as the index you are trying to create, EMPLOYEE_PK.

Tom Kytes states on his ask tom site:

A primary key or unique constraint is not guaranteed to create a new index, nor is the index they create guaranteed to be a unique index. Therefore, if you desire a unique index to be created for query performance issues, you should explicitly create one.

Oddly, enough when I run your DDL, I receive the ORA-00955 error and not the ORA-00911 (invalid character error).

Often times the ORA-00911 error occurs when one is copying from one editor to another and you copy some non-printable characters.

Below, I ran the first DDL statement provided and then I checked the indices created. If you look closely, you will see that EMPLOYEE_PK index was created as a consequence of the primary key constraint which you created.

    SCOTT@dev> create table EMPLOYEE (
      2  EMPLOYEE_KEY       NUMBER(10) not null,
      3  SALARY             NUMBER(10,2),
      4  LAST_NAME          VARCHAR2(132),
      5  FIRST_NAME         VARCHAR2(132),
      6  SUPERVISOR_KEY     NUMBER(10),
      7  constraint EMPLOYEE_PK primary key (EMPLOYEE_KEY)
      8  );

    Table created.



    SCOTT@dev> SELECT ind.index_name,
  2    ind.index_type,
  3    ind.table_owner,
  4    ind.table_name
  5  FROM all_indexes ind
  6  JOIN all_ind_columns icol
  7  ON ind.owner       = icol.index_owner
  8  AND ind.table_name = icol.table_name
  9  AND ind.index_name = icol.index_name
 10  WHERE 1            = 1
 11  AND ind.table_name = 'EMPLOYEE'
 12  /


    INDEX_NAME                     INDEX_TYPE                  TABLE_OWNER                    TABLE_NAME
    ============================== =========================== ============================== ==============================
    EMPLOYEE_PK                    NORMAL                      SCOTT                          EMPLOYEE

If you desire to create a unique index on EMPLOYEE_KEY separately, a number of approaches could be taken. Here is one:

    --create the table

    SCOTT@dev> create table EMPLOYEE (
      2  EMPLOYEE_KEY       NUMBER(10) not null,
      3  SALARY             NUMBER(10,2),
      4  LAST_NAME          VARCHAR2(132),
      5  FIRST_NAME         VARCHAR2(132),
      6  SUPERVISOR_KEY     NUMBER(10)
      7  );

    Table created.

    --create the unique index

    SCOTT@dev> CREATE UNIQUE INDEX EMPLOYEE_PK ON EMPLOYEE (EMPLOYEE_KEY);

    Index created.

    --add the primary key

    SCOTT@dev> alter table EMPLOYEE add
      2  constraint EMPLOYEE_PK primary key (EMPLOYEE_KEY)
      3  /

    Table altered.

SCOTT@dev> SELECT ind.index_name,
  2    ind.index_type,
  3    ind.table_owner,
  4    ind.table_name
  5  FROM all_indexes ind
  6  JOIN all_ind_columns icol
  7  ON ind.owner       = icol.index_owner
  8  AND ind.table_name = icol.table_name
  9  AND ind.index_name = icol.index_name
 10  WHERE 1            = 1
 11  AND ind.table_name = 'EMPLOYEE'
 12  /

    INDEX_NAME                     INDEX_TYPE                  TABLE_OWNER                    TABLE_NAME
    ============================== =========================== ============================== ==============================
    EMPLOYEE_PK                    NORMAL                      SCOTT                          EMPLOYEE
    SCOTT@dev> 
 SCOTT@dev> SELECT cons.constraint_name
  2  FROM all_constraints cons
  3  JOIN all_cons_columns conc
  4  ON conc.table_name       = 'EMPLOYEE'
  5  AND cons.owner           = conc.owner
  6  AND cons.table_name      = conc.table_name
  7  WHERE 1                  = 1
  8  AND cons.constraint_name = conc.constraint_name
  9  /

    CONSTRAINT_NAME                
    =============================
    EMPLOYEE_PK