ORA-00907: missing right parenthesis but I cant see it?-CodePudding

So this is my code for a homework

CREATE TABLE workorders
(
wo# NUMBER(5) PRIMARY KEY,
proj# VARCHAR(10) NOT NULL FOREIGN KEY REFERENCES project(proj#),
wo_desc VARCHAR(30) NOT NULL UNIQUE,
wo_assigned VARCHAR(30),
wo_hours NUMBER(5) NOT NULL CHECK(wo_hours>0),
wo_start DATE,
wo_due DATE,
wo_complete CHAR(1),
CONSTRAINT workorders_wo_complete_chk CHECK(wo_complete in('Y','N'))
);

I could not figure out why oracle apex won't let me create this table, it says

ORA-00907: missing right parenthesis

But I double-checked so many times and I think I do have all the parenthesis? What did I do wrong here?

Thanks in advance

I just want to create this table under these constreaints but I could find any errors that I know of.

CodePudding user response：

Oracle's error messages are often not particularly helpful, and the best thing to do is to go through your code, line by line, commenting out each line until you isolate the problem.

In this case the offending line is

proj# VARCHAR(10) NOT NULL FOREIGN KEY REFERENCES project(proj#),

and the problem is that you don't use the words FOREIGN KEY to define a foreign key - you just need to specify what it references. So this should be

proj# VARCHAR(10) NOT NULL REFERENCES project(proj#),

and then you'll be fine.

db<>fiddle here

CodePudding user response：

When defining an inline constraint, the FOREIGN KEY terms are not used:

proj# VARCHAR2(10) NOT NULL
                   REFERENCES project(proj#)

Note: Oracle uses VARCHAR2 and VARCHAR is an alias to VARCHAR2 and it is considered best-practice to use VARCHAR2 throughout.

However, those keywords are required when you define an out-of-line foreign key (but then you would be missing a comma and the column identifier):

proj# NOT NULL,
FOREIGN KEY (proj#) REFERENCES project (proj#)

Note: If you are using a foreign key constraint then you do not need to define the data type and Oracle will implicitly use the data type of the column being referenced.

The complete code using (named) inline constraints throughout would be:

CREATE TABLE workorders
(
  wo#         NUMBER(5)
              CONSTRAINT workorders__wo#__pk PRIMARY KEY,
  proj#       -- Note: do not need the data type as the FK will define it.
              NOT NULL
              CONSTRAINT workorders__proj#__fk REFERENCES project(proj#),
  wo_desc     VARCHAR2(30)
              NOT NULL
              CONSTRAINT workorders__wo_desc__u UNIQUE,
  wo_assigned VARCHAR2(30),
  wo_hours    NUMBER(5)
              NOT NULL
              CONSTRAINT workorders__wo_hours__chk CHECK(wo_hours>0),
  wo_start    DATE,
  wo_due      DATE,
  wo_complete CHAR(1)
              CONSTRAINT workorders_wo_complete_chk CHECK(wo_complete in('Y','N'))
);

and using (named) out-of-line constraints throughout:

CREATE TABLE workorders
(
  wo#         NUMBER(5),
  proj#       -- Note: do not need the data type as the FK will define it.
              NOT NULL,
  wo_desc     VARCHAR2(30)
              NOT NULL,
  wo_assigned VARCHAR2(30),
  wo_hours    NUMBER(5)
              NOT NULL,
  wo_start    DATE,
  wo_due      DATE,
  wo_complete CHAR(1),
  CONSTRAINT workorders__wo#__pk PRIMARY KEY(wo#),
  CONSTRAINT workorders__proj#__fk FOREIGN KEY (proj#) REFERENCES project(proj#),
  CONSTRAINT workorders__wo_desc__u UNIQUE(wo_desc),
  CONSTRAINT workorders_wo_complete_chk CHECK(wo_complete in('Y','N')),
  CONSTRAINT workorders__wo_hours__chk CHECK(wo_hours>0)
);

fiddle