CODEWITHSUNDEEP
sqloracle-database
Zbarcea Christian
Zbarcea Christian

Reputation: 9548

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
);

Upvotes: 1

Views: 38089

Answers (4)

Aman
Aman

Reputation: 1

I am creating a table of employee Create table employee ( emp_id number(10), emp_name varchar(10), salary number(10)); I am getting an error of invalid identifier

Upvotes: 0

user1088172
user1088172

Reputation:

I think there some mistake here :

Catch this example : 

CREATE TABLE "name_of_table"

    ("column_1" "data_type",

    "column_2" "data_type",

    "column_3" "data_type",

    CONSTRAINT column_name PRIMARY KEY (column_1, column_2)

    ... );

Your code suppose :

CREATE TABLE animals(

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
);

You may check the example here

Upvotes: 0

Yasir Arsanukayev
Yasir Arsanukayev

Reputation: 9686

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.

Upvotes: 8

Robert
Robert

Reputation: 25763

This is correct code, you must remove CONSTRAINT word:

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
);

but you can also use constraints for PK and UNIQUE as below:

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

It is good practice to use constraints because they give you a friendly name/short description.

SQL Fiddle DEMO

Upvotes: 2

Related Questions