SQL错误：ORA-02000：在创建基于标识列的表时缺少ALWAYS关键字 [英] SQL Error: ORA-02000: missing ALWAYS keyword when create identity column based table

问题描述

我尝试在表格上创建一个自动递增的列，正如我在发布有两种方式，第二次使用Identity列实现更优雅的解决方案，但是当我尝试实现它时，我收到以下错误：

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"

实际表脚本实现：

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.

问题是我使用的是旧版本的Oracle，11g。

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

推荐答案

在版本 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.

SQL>

这篇关于SQL错误：ORA-02000：在创建基于标识列的表时缺少ALWAYS关键字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！