Getting invalid dataype when creating table in Oracle

Question

So, I added a column in a table called FACILITY. Then, I tried to CREATE a table called CLUBMANAGER, and am greeted with the message:

ERROR at line 2:
ORA-00902: invalid datatype

My SQL statements are:

CREATE TABLE FACILITY(
facilityName    VARCHAR2(50)    NOT NULL,
rate            NUMBER(7,2),
status          VARCHAR2(25),
CONSTRAINT FACILITY_PKEY PRIMARY KEY(facilityName) );

ALTER TABLE FACILITY ADD MANAGER_EMAIL VARCHAR2(100);
ALTER TABLE facility ADD CONSTRAINT pk_fac UNIQUE (manager_email);
CREATE TABLE CLUBMANAGER (
    email       VARCHAR2(100)   NOT NULL,
    name        VARCHAR2(100)   NOT NULL,
    phoneNum    NUMBER(10)      NOT NULL,
    CONSTRAINT  CLUBMANAGER_PKEY PRIMARY KEY (email),
    CONSTRAINT  CLUBMEMBER_FKEY FOREIGN KEY (email) REFERENCES FACILITY (MANAGER_EMAIL));

Line 2 was:

email       VARCHAR2(100)   NOT NULL

My SQL Output is:

    SQL> ALTER TABLE facility ADD CONSTRAINT pk_fac UNIQUE (manager_email);

Table altered.

SQL> CREATE TABLE CLUBMANAGER (
  2  emailVARCHAR(100)NOT NULL,
  3  nameVARCHAR(100)NOT NULL,
  4  phoneNumNUMBER(10)NOT NULL,
  5  CONSTRAINTCLUBMANAGER_PKEY PRIMARY KEY (email),
  6  CONSTRAINTCLUBMEMBER_FKEY FOREIGN KEY (email) REFERENCES FACILITY (MANAGER_EMAIL));
emailVARCHAR(100)NOT NULL,
            *
ERROR at line 2:
ORA-00902: invalid datatype

I am using Oracle Database XE 11.2.

dbfiddle.uk/… - check here it works both for varchar and varchar2 — Fahmi
– Fahmi, Commented Nov 8, 2018 at 12:55
you need spaces between column names and data types and constraints such as email VARCHAR(100) NOT NULL. — Barbaros Özhan
– Barbaros Özhan, Commented Nov 8, 2018 at 13:22

Littlefoot · Accepted Answer · 2018-11-08 13:01:35Z

1

From what you posted, there's no invalid datatype (at least, I don't see any), but missing primary or unique key - you can't create a foreign key constraint which doesn't point to one of those keys.

Have a look: first, a dummy FACILITY table, altered as you did it:

SQL> CREATE TABLE facility (id NUMBER);

Table created.

SQL> ALTER TABLE facility ADD manager_email VARCHAR2 (100);

Table altered.

Create table CLUBMANAGER:

SQL> CREATE TABLE clubmanager
  2  (
  3     email      VARCHAR2 (100) NOT NULL,
  4     name       VARCHAR2 (100) NOT NULL,
  5     phonenum   NUMBER (10) NOT NULL,
  6     CONSTRAINT clubmanager_pkey PRIMARY KEY (email),
  7     CONSTRAINT clubmember_fkey FOREIGN KEY
  8        (email)
  9         REFERENCES facility (manager_email)
 10  );
       REFERENCES facility (manager_email)
                            *
ERROR at line 9:
ORA-02270: no matching unique or primary key for this column-list

Nope, won't work. Add a unique key and crate CLUBMANAGER once again:

SQL> ALTER TABLE facility ADD CONSTRAINT pk_fac UNIQUE (manager_email);

Table altered.

SQL> CREATE TABLE clubmanager
  2  (
  3     email      VARCHAR2 (100) NOT NULL,
  4     name       VARCHAR2 (100) NOT NULL,
  5     phonenum   NUMBER (10) NOT NULL,
  6     CONSTRAINT clubmanager_pkey PRIMARY KEY (email),
  7     CONSTRAINT clubmember_fkey FOREIGN KEY
  8        (email)
  9         REFERENCES facility (manager_email)
 10  );

Table created.

SQL>

As you see, everything is OK. If it still doesn't work at your side, please, post your own SQL*Plus session so that we could see what you did and how Oracle responded.

answered Nov 8, 2018 at 13:01

Littlefoot

144k15 gold badges42 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Azaqi Over a year ago

I've followed your steps, and made manager_email unique. I tried running the CREATE statement again, and am greeted with the same error. I've posted the edits on top.

Littlefoot Over a year ago

I ran your code (Oracle 11g), no problem whatsoever. But, look how error report looks like: emailVARCHAR(100)NOT NULL, - no space at all. Did you, by any chance, write that query in MS Word (or some similar text editor)? Try to type the command directly into SQL*Plus, don't copy/paste it from somewhere else.

Azaqi Over a year ago

I typed the query in Notepad++. I'm supposed to make a script, and run that script on Oracle. Even with the script, it shows the same error.

Littlefoot Over a year ago

OK. Just to make sure, please, type everything into SQL*Plus - actually, you don't even have to type everything, just CREATE TABLE CLUBMANAGER (email VARCHAR2(100) NOT NULL);

Littlefoot Over a year ago

You're welcome. Huh, TAB ... right; it looks OK, but it is misleading. I'm glad you fixed it!

|

Barbaros Özhan · Accepted Answer · 2018-11-08 13:26:12Z

0

It's interesting you to get ORA-00902.

EDIT : you need spaces between column names and data types and constraints such as email VARCHAR(100) NOT NULL instead of emailVARCHAR(100)NOT NULL

The column MANAGER_EMAIL needs to be a unique or primary key as being able to be referenced by a foreign key .

So, use ALTER TABLE FACILITY ADD MANAGER_EMAIL VARCHAR2(100) primary key [unique] ;

edited Nov 8, 2018 at 13:26

answered Nov 8, 2018 at 13:00

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

Collectives™ on Stack Overflow

Getting invalid dataype when creating table in Oracle

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related