CODEWITHSUNDEEP
sqloracle-database
Azaqi
Azaqi

Reputation: 125

Getting invalid dataype when creating table in Oracle

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.

Upvotes: 1

Views: 597

Answers (2)

Barbaros Özhan
Barbaros Özhan

Reputation: 65105

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

Upvotes: 0

Littlefoot
Littlefoot

Reputation: 142710

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.

Upvotes: 1

Related Questions