I'm getting an error on Oracle Apex ORA-00907: missing right parenthesis

Question

I'm trying to run this snipped of SQL in Oracle Apex and keep receiving errors about right parenthesis. I have tried to remove the constraints or alter the tables later, but I keep coming up with the same sort of errors. I cannot seem to figure out what it is that's wrong with the table structure, and I can't find anything online about it that makes sense. Any help would be much appreciated, thanks. Code below...

DROP TABLE Employee_T
CASCADE CONSTRAINTS;
DROP TABLE TaxDepartment_T
CASCADE CONSTRAINTS;
DROP TABLE Location_T
CASCADE CONSTRAINTS;



CREATE TABLE Employee_T
(
    EmployeeID NUMBER(11) NOT NULL,
    EmployeeName VARCHAR2(25) NOT NULL,
    EmployeeAddress VARCHAR2(30)    ,
    EmployeeCity VARCHAR2(20)    ,
    EmployeeState CHAR(2)         ,
    EmployeePostalCode VARCHAR2(10)    ,
    CONSTRAINT Employee_PK PRIMARY KEY(EmployeeID),
    CONSTRAINT Employee_FK1 FOREIGN KEY(DepartmentID) REFERENCES (TaxDepartment_T),
    CONSTRAINT Employee_FK2 FOREIGN KEY(BranchID) REFERENCES (Location_T)
);



CREATE TABLE TaxDepartment_T
(
    DepartmentID INTEGER(11) NOT NULL,
    BranchID INTEGER(11) NOT NULL,
    CPAID INTEGER(11) NOT NULL,
    EmployeeID INTEGER(11) NOT NULL,
    BranchName VARCHAR2(50) NOT NULL,
    CONSTRAINT TaxDepartment_PK PRIMARY KEY(DepartmentID, BranchID, CPAID),
    CONSTRAINT TaxDepartment_FK1 FOREIGN KEY(BranchID) REFERENCES (Location_T),
    CONSTRAINT TaxDepartment_FK2 FOREIGN KEY(EmployeeID) REFERENCES (Employee_T)
);



CREATE TABLE Location_T
(
    BranchID INTEGER(11) NOT NULL,
    BranchName VARCHAR2(50) NOT NULL,
    ManagerName VARCHAR2(50) NOT NULL,
    EmployeeID INTEGER(11) NOT NULL,
    CONSTRAINT Location_PK PRIMARY KEY(BranchID),
    CONSTRAINT Location_FK1 FOREIGN KEY(EmployeeID) REFERENCES (Employee_T)
);

`integer` is OK but not `integer(11)`. Use `number(11)`. Also while `char(2)` is valid, you should use the standard type for strings, `varchar2(2)`. — William Robertson, May 12 '20 at 06:37
All of your `references(table name)` constraints should be `references tablename`, or `references tablename (columnname)`. Why do Tax Department and Location have employee IDs? Also Employee FK1 and FK2 refer to `DepartmentID` and `BranchID` columns that the table doesn't have. — William Robertson, May 12 '20 at 06:52

thatjeffsmith · Accepted Answer · 2020-05-12T12:28:52.250

Your foreign key constraint syntax is off.

What it should look like:

REFERENCES SCHEMA.TABLE (COLUMN)

and you just have:

REFERENCES (COLUMN)

If you look at this code in SQL Developer, the parser catches your issue right away, and even gives you a simple click to get to the Docs with syntax diagram for defining FK constraints.

This is your FIRST problem.

The fun with bugs is killing one only exposes the next one. You can't create FK constraints for tables you haven't created yet. So either you need to create the base tables first, OR you need to remove the FK constraints from your CREATE TABLE calls, and add them back later as

alter table TABLE_NAME add constraint CONSTRAINT_NAME foreign key(COLUMN_NAME) references TABLE_NAME2(COLUMN_NAME)

Place all of these ALTER TABLE ADD CONSTRAINT calls at the end of your script, once all the tables have already been created.

Someone else has also noticed that you're using INTEGER.

Which I do, ALL THE TIME...because I'm too lazy to type 'NUMBER(38,0)'

That's fine. But what you can't do is say INTEGER(9). That makes no sense in Oracle.

score 1 · Answer 2 · answered May 11 '20 at 22:57

1

You should use NUMBER instead of INTEGER in TaxDepartment_T and Location_T tables

answered May 11 '20 at 22:57

antoine.lange

731
6
24

Integer is fine, it's just an alias for number(38,0) in Oracle. – thatjeffsmith May 11 '20 at 23:48
3

`integer` is fine, but `integer(11)` gives "ORA-00907: missing right parenthesis". – William Robertson May 12 '20 at 06:33

William Robertson · Answer 3 · 2020-05-12T11:16:19.697

There are a few syntax issues.

integer is OK but not integer(11). Use number(11). (Also, while char is a valid type, you should stick to the standard varchar2 for strings to avoid unexpected behaviour.)
Foreign key constraints are written constraint fk references tablename, or optionally you can specify the referenced column in brackets: constraint fk references tablename (columnname). (Also if you write them inline as part of the column definition, you can let the datatype inherit from the parent.)
Employee FK1 and FK2 refer to DepartmentID and BranchID columns that the table doesn't have.
You need to put the parent before the child if you want to run it as a script.

I would write it like this:

drop table employee_t cascade constraints;
drop table taxdepartment_t cascade constraints;
drop table location_t cascade constraints;

create table Location_T
( BranchID           number(11) not null constraint Location_PK primary key
, BranchName         varchar2(50) not null
, ManagerName        varchar2(50) not null );

create table TaxDepartment_T
( DepartmentID       number(11) not null
, BranchID           constraint TaxDepartment_Location_FK references location_t not null
, CPAID              number(11) not null
, BranchName         varchar2(50) not null
, constraint TaxDepartment_PK primary key(DepartmentID, BranchID, CPAID) );

create table Employee_T
( EmployeeID         number(11) not null constraint Employee_PK primary key
, EmployeeName       varchar2(25) not null
, EmployeeAddress    varchar2(30)
, EmployeeCity       varchar2(20)
, EmployeeState      varchar2(2)
, EmployeePostalCode varchar2(10)
, DepartmentID       constraint Employee_Department_FK references location_t
, BranchID           constraint Employee_Branch_FK references Location_T );

I don't think Location or Tax Department should have EmployeeId columns so I removed them - say if you think that's wrong.

Personally I wouldn't put _T on the end of my table names, and I would avoid camelCase naming because the data dictionary doesn't retain it, and so describing a table gives for example:

SQL> @desc location_t
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 BRANCHID                                  NOT NULL NUMBER(11)
 BRANCHNAME                                NOT NULL VARCHAR2(50)
 MANAGERNAME                               NOT NULL VARCHAR2(50)

I'm getting an error on Oracle Apex ORA-00907: missing right parenthesis

3 Answers3