Oracle table create error ORA-00904 invalid identifier

It’s very interesting I don’t know why I’m getting ORA-00904 invalid identifier when I’m trying to create a table with oracle.

CREATE TABLE animals
(
   CONSTRAINT animal_id NUMBER(6) PRIMARY_KEY,
   name VARCHAR2(25),
   CONSTRAINT license_tag_number NUMBER(10) UNIQUE,
   admit_date DATE NOT NULL,
   adoption_id NUMBER(5),
   vaccination_date DATE NOT NULL
);

Answer

When creating tables with CREATE TABLE in Oracle, you have at least four ways to specify constraints.

In-line specification

CREATE TABLE animals
(
   animal_id NUMBER(6) PRIMARY KEY,
   name VARCHAR2(25),
   license_tag_number NUMBER(10) UNIQUE,
   admit_date DATE NOT NULL,
   adoption_id NUMBER(5),
   vaccination_date DATE NOT NULL
);

In-line specification with explicit constraints’ names

CREATE TABLE animals
(
   animal_id NUMBER(6) CONSTRAINT animal_id_pk PRIMARY KEY,
   name VARCHAR2(25),
   license_tag_number NUMBER(10) CONSTRAINT animal_tag_no_uq UNIQUE,
   admit_date DATE NOT NULL,
   adoption_id NUMBER(5),
   vaccination_date DATE NOT NULL
);

Out-line specification

CREATE TABLE animals
(
   animal_id NUMBER(6) ,
   name VARCHAR2(25),
   license_tag_number NUMBER(10),
   admit_date DATE NOT NULL,
   adoption_id NUMBER(5),
   vaccination_date DATE NOT NULL,
   PRIMARY KEY (animal_id),
   UNIQUE (license_tag_number)
);

Out-line specification with explicit constraints’ names

CREATE TABLE animals
(
   animal_id NUMBER(6) ,
   name VARCHAR2(25),
   license_tag_number NUMBER(10),
   admit_date DATE NOT NULL,
   adoption_id NUMBER(5),
   vaccination_date DATE NOT NULL,
   CONSTRAINT animal_id_pk PRIMARY KEY (animal_id),
   CONSTRAINT animal_tag_no_uq UNIQUE (license_tag_number)
);

If you don’t explicitly specify constraints names, they are generated automatically by the system, and read something like SYS_C0013321. I find the last way the most readable, because you see which constraints are created, and can manage them using user-friendly names (e. g. using view user_constraints).

By the way, there’s a typo in your code: you should use PRIMARY KEY instead of PRIMARY_KEY.