Error creating an Identity in Oracle

Navigation
1

How about people, I hope you can help me solve this problem even if it's silly, I'm trying to create a table with an Identity type ID and I'm getting the following error: 

Error que empieza en la línea: 1 del comando :
CREATE TABLE USUARIO (
userID NUMBER GENERATED ALWAYS AS IDENTITY (START WITH 100 INCREMENT BY 1),
username VARCHAR2(32) NOT NULL,
pass VARCHAR2(32) NOT NULL,
nickname VARCHAR2(32) NOT NULL,
email VARCHAR2(32) NOT NULL,
userType CHAR(5) CONSTRAINT CH_userType CHECK(userType IN ('comun', 'boss')),
CONSTRAINT PK_usuario
  PRIMARY KEY (userID)
);
Informe de error -
ORA-02000: missing ( keyword
02000. 00000 -  "missing %s keyword"

It seems I'm missing a "(" but I can not see where, everything seems to be in order, I hope you can help me, thanks in advance.

    
asked by Cutu 05.05.2017 в 18:54
source

1 answer

0

The error is due to the version of Oracle that you use. The ability to define a column as a IDENTITY is new with version 12.

In previous versions, the best thing you can do to approximate the functionality is to define a SEQUENCE that you use to assign to the column by means of a trigger.

Here is an example: 

create sequence usuario_seq START WITH 100 INCREMENT BY 1;
/

CREATE TABLE USUARIO (
userID NUMBER,
username VARCHAR2(32) NOT NULL,
pass VARCHAR2(32) NOT NULL,
nickname VARCHAR2(32) NOT NULL,
email VARCHAR2(32) NOT NULL,
userType CHAR(5) CONSTRAINT CH_userType CHECK(userType IN ('comun', 'boss')),
CONSTRAINT PK_usuario
  PRIMARY KEY (userID)
);
/

create trigger usuario_trigger
before insert on usuario
for each row
begin
  :new.userID := usuario_seq.nextval;
end;
/

References:

answered by 05.05.2017 в 21:15
No value specified for parameter 10 when loading date from MySQL in JDateChooser ChartJs, Draw straight lines in bar chart [closed]