CODEWITHSUNDEEP
sqloracle-database
Kavindu Gayan
Kavindu Gayan

Reputation: 394

SQL Error: ORA-00904: : invalid identifier 3

I am trying to create a table (customer) to database by using this query,

CREATE TABLE customers(
  "customer_id" VARCHAR2(20),
  f_name VARCHAR2(30),
    CONSTRAINT f_name_not_null NOT NULL,
  l_name VARCHAR2(30),
CONSTRAINT l_name_not_null NOT NULL,
  mobile_no VARCHAR2(30),
    CONSTRAINT mobile_no_not_null NOT NULL,
  address VARCHAR2(30),
  CONSTRAINT address_not_null NOT NULL,
  CONSTRAINT customer_pk PRIMARY KEY(customer_id),
  CONSTRAINT mobile_no_address_unique UNIQUE(mobile_no,address));

i get the following back:

Error report - SQL Error: ORA-00904: : invalid identifier 00904. 00000 - "%s: invalid identifier"

Upvotes: 1

Views: 1077

Answers (1)

Marmite Bomber
Marmite Bomber

Reputation: 21063

There are two problems in this statement

first is the inconsistent usage of quoted column (as noted in comment by @a_horse_with_no_name )

second problem is that the column constraint should not be separated with a comma from the column definition

The correct statement with changes in comments is

CREATE TABLE customers(
  /*"*/customer_id/*"*/ VARCHAR2(20),
  f_name VARCHAR2(30) /*,*/
    CONSTRAINT f_name_not_null NOT NULL,
  l_name VARCHAR2(30)/*,*/
CONSTRAINT l_name_not_null NOT NULL,
  mobile_no VARCHAR2(30)/*,*/
    CONSTRAINT mobile_no_not_null NOT NULL,
  address VARCHAR2(30) /*,*/
  CONSTRAINT address_not_null NOT NULL,
  CONSTRAINT customer_pk PRIMARY KEY(customer_id),
  CONSTRAINT mobile_no_address_unique UNIQUE(mobile_no,address));

Interesting is that the error you see concerns the second issue, after resolving it you will get the error for the first issue which is more specific:

 ORA-00904: "CUSTOMER_ID": invalid identifier

Upvotes: 1

Related Questions