Reputation: 9548
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
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
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
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
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.
Upvotes: 2