Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.5k views
in Technique[技术] by (71.8m points)

java - SQL Error: ORA-02000: missing ALWAYS keyword when create identity column based table

I try to create an auto incremented column on a table and as I see in this post there are 2 ways, the second implementation with the Identity column is a more elegant solution, but when I try to implement it I get the following error:

    Error at Command Line : 3 Column : 31
    Error report -
    SQL Error: ORA-02000: missing ALWAYS keyword
    02000. 00000 -  "missing %s keyword"

Actual table script implementation:

CREATE TABLE "PLATFORM"."AUTH_PERMISSION"
(
    ID NUMBER(19,0) GENERATED BY DEFAULT ON NULL AS IDENTITY, 
    -- ID NUMBER(19,0) PRIMARY KEY NOT NULL,
    NAME VARCHAR2(50) UNIQUE NOT NULL,
    ACTION_ID NUMBER(19,0) NOT NULL,
    RESOURCE_ID NUMBER(19,0) NOT NULL,
    ENVIRONMENT_ID NUMBER(19,0) NOT NULL,
    CONSTRAINT "ACTION_ID" FOREIGN KEY ("ACTION_ID")
      REFERENCES "AUTH_ACTION" ("ID") ENABLE,
    CONSTRAINT "ENVIRONMENT_ID" FOREIGN KEY ("ENVIRONMENT_ID")
      REFERENCES "AUTH_ENVIRONMENT" ("ID") ENABLE,
    CONSTRAINT "RESOURCE_ID" FOREIGN KEY ("RESOURCE_ID")
      REFERENCES "AUTH_RESOURCE" ("ID") ENABLE,
    UNIQUE (ACTION_ID, ENVIRONMENT_ID, RESOURCE_ID)
);

It can bee seen that the column that I try to auto-increment is the primary key of the table.

This is the reference from where I got the solution.

The problem was that I used an older version of Oracle, 11g.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

Perhaps the Oracle database (server) you are trying to connect to is 12c, however the client (installed locally) you are using doesn't support the feature. Please check your Oracle client version, it could be 11g or lower which doesn't support it. You need to download a higher client version.

Works perfectly on version 12.1.0.1.

SQL> select banner from v$version where rownum = 1;

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

SQL> CREATE TABLE AUTH_PERMISSION
  2  (
  3      ID NUMBER(19,0) GENERATED BY DEFAULT ON NULL AS IDENTITY,
  4      -- ID NUMBER(19,0) PRIMARY KEY NOT NULL,
  5      NAME VARCHAR2(50) UNIQUE NOT NULL,
  6      ACTION_ID NUMBER(19,0) NOT NULL,
  7      RESOURCE_ID NUMBER(19,0) NOT NULL,
  8      ENVIRONMENT_ID NUMBER(19,0) NOT NULL
  9  );

Table created.

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...