SQL Error: ORA-02000: missing ALWAYS keyword

Question

i use Oracle version 11g and i want to ad HR db but when i try to create Table this error appear .

CREATE TABLE regions
  (
    region_id NUMBER GENERATED ALWAYS BY DEFAULT AS IDENTITY
    START WITH 5 PRIMARY KEY,
    region_name VARCHAR2( 50 ) NOT NULL
  );

how can i resolve this issue without changing my oracle version?

Your DDL is in incorrect format. I think you want auto increment ID. If that, you should look up this link: [https://stackoverflow.com/questions/11296361/how-to-create-id-with-auto-increment-on-oracle](https://stackoverflow.com/questions/11296361/how-to-create-id-with-auto-increment-on-oracle) — tungns, Sep 07 '20 at 03:47
Does this answer your question? [How to create id with AUTO\_INCREMENT on Oracle?](https://stackoverflow.com/questions/11296361/how-to-create-id-with-auto-increment-on-oracle) — Chris Schaller, Sep 07 '20 at 08:40

score 1 · Answer 1 · edited Sep 07 '20 at 08:34

Identity has been introduced as part of Oracle 12c and not available in 11g, so for auto-increment ID prior to 12c you can use this post

Developers who are used to AutoNumber columns in MS Access or Identity columns in SQL Server often complain when they have to manually populate primary key columns using sequences in Oracle.

This type of functionality is easily implemented in Oracle using triggers.

Create a table with a suitable primary key column and a sequence to support it.

CREATE TABLE departments (
  ID           NUMBER(10)    NOT NULL,
  DESCRIPTION  VARCHAR2(50)  NOT NULL);

ALTER TABLE departments ADD (
  CONSTRAINT dept_pk PRIMARY KEY (ID));

CREATE SEQUENCE dept_seq;

Create a trigger to populate the ID column if it's not specified in the insert.

CREATE OR REPLACE TRIGGER dept_bir 
BEFORE INSERT ON departments 
FOR EACH ROW
WHEN (new.id IS NULL)
BEGIN
  SELECT dept_seq.NEXTVAL
  INTO   :new.id
  FROM   dual;
END;
/

score 1 · Answer 2 · answered Sep 07 '20 at 08:11

In Oracle version pre 12c, you should use a SEQUENCE and TRIGGER to handle your auto-number ID

Table

  CREATE TABLE regions 
  (
      region_id    NUMBER(10)    NOT NULL,
      region_name  VARCHAR2(50)  NOT NULL
  );

ALTER TABLE regions ADD (
  CONSTRAINT regions_pk PRIMARY KEY (ID));

Sequence:

CREATE SEQUENCE regions_seq;

Trigger:

CREATE OR REPLACE TRIGGER regions_id_generate 
BEFORE INSERT ON regions 
FOR EACH ROW
WHEN (new.region_id IS NULL)
BEGIN
  SELECT regions_seq.NEXTVAL
  INTO   :new.region_id
  FROM   dual;
END;
/

When you do an insert, just specify a NULL value for your region_ID column, and Oracle will attribute it the next integer number in the sequence

score 0 · Answer 3 · answered Sep 07 '20 at 03:56

Im sorry, but maybe the server/database you are trying to connect to is 12c and yours/client doesnt support the feature. (I believe the IDENTITY definition is introduced in 12c)

Maybe try to use SEQUENCE instead. (Sequence is an object that is not bound to a specific table and can be used anywhere to get new unique numbers. Because of this you should create a trigger to set the value to your column)

SQL Error: ORA-02000: missing ALWAYS keyword

3 Answers3