Oracle SQL "invalid identifier error" when trying to create a table

Question

I have successfully built these tables:

CREATE TABLE Tables
(
    tnum NUMBER(4) PRIMARY KEY,
    floor NUMBER(1), CHECK (floor >= 0 AND floor <= 4),
    capacity NUMBER(2), CHECK(capacity >= 0 AND capacity <= 25),
    location VARCHAR(50)
);

CREATE TABLE Customer
(
    name VARCHAR(25),
    phone VARCHAR(16),
    city VARCHAR(25),
    PRIMARY KEY(name, phone)
);

CREATE TABLE Menu 
(
    pid NUMBER(4) PRIMARY KEY,
    pname VARCHAR(25),
    price NUMBER(4, 1)
);

BUT when I try build this one, I got an error:

Error report - ORA-00904: : invalid identifier 00904. 00000 - "%s: invalid identifier" *Cause: *Action:

CREATE TABLE Orders
(
orderid NUMBER(4) PRIMARY KEY,
custname VARCHAR(25), 
                      CONSTRAINT fk_customer 
                      FOREIGN KEY (custname) 
                      REFERENCES Customer(customer.name),
phone VARCHAR(16), 
                  CONSTRAINT fk_customer
                  FOREIGN KEY(phone)
                  REFERENCES Customer(customer.phone),
date DATE,
tnum NUMBER(4),
               CONSTRAINT fk_tables
               FOREIGN KEY(tnum)
               REFERENCES Tables(tables.tnum),
numofdiners NUMBER(2)
);

I tried to understand what can cause the problem, even though I checked this link and some more other links, I didn't manage to solve the problem.

REFERENCES Tables(tnum) ...

jarlh
– jarlh

2019-12-12 13:32:47 +00:00
Commented Dec 12, 2019 at 13:32 — jarlh
– jarlh, Commented Dec 12, 2019 at 13:32

score 3 · Accepted Answer · 2019-12-12 13:45:10Z

You can specify a column level constraint directly after the column, but then you have to get rid of the comma and the foreign key part as it's clear which column is meant.

But you need to include all columns of the primary key in the foreign key definition. And because the primary key of the customer table consist of two columns, you can't use an inline foreign key in the table orders.

The foreign key "references" part needs to specify the column name in the target table without a prefix for the table (the table is already clear because of the references table_xxx part, so there is no need to prefix the target column with the table name).

Additionally DATE is a reserved keyword.

CREATE TABLE Orders
(
  orderid          NUMBER(4) PRIMARY KEY,
  custname         VARCHAR(25) not null
  phone            VARCHAR(16) not null,
  reservation_date DATE,
  tnum             NUMBER(4)  --<< no comma here!
                     -- no FOREIGN KEY keyword here
                     CONSTRAINT fk_tables
                     REFERENCES Tables(tnum), --<< only the column name between the (...)
  numofdiners      NUMBER(2),  --<< you need a comma before the table level constraints

  CONSTRAINT fk_customer
    FOREIGN KEY(name, phone) --<< list both columns here
    REFERENCES Customer(name, phone) --<< and here 
);

Note that the columns in a multi-column foreign key are matched by position, not by name.
Therefore, the following would be wrong

CONSTRAINT fk_customer
  FOREIGN KEY(phone, name)
  REFERENCES Customer(name, phone)

Collectives™ on Stack Overflow

Oracle SQL "invalid identifier error" when trying to create a table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related