SQL Foreign Key Help. ORA-00907 Missing right parenthesis

Question

I am new to SQL and I am having trouble with the foreign keys in the CourseEnrollments table.

I keep receiving an error report - ORA-00907: missing right parenthesis 00907. 00000 - "missing right parenthesis *Cause: *Action:

It appears that all the commas and parenthesis are in place.

Create Table Students 
(
StudentID INT NOT NULL,
Lastname VARCHAR2(25),
Firstname VARCHAR2(20),
Email VARCHAR2(40),
Enrollmentdate DATE,
CONSTRAINT student_pk PRIMARY KEY (StudentID)
);

/* This is the creation of the Students table */

Create Table Faculty 
(
FacultyID INT NOT NULL,
Lastname VARCHAR2(25),
Firstname VARCHAR2(20),
Email VARCHAR2(40),
Hiredate DATE,
CONSTRAINT faculty_pk PRIMARY KEY (FacultyID)
);

/* This is the creation of the Faculty table */

Create Table Courses 
(
CourseID INT NOT NULL,
"Subject (e.g. SDEV)" VARCHAR2(20),
"Catalognbr (e.g. 350)" NUMBER,
"Title (e.g. Database Security)" VARCHAR2(40),
CONSTRAINT course_pk PRIMARY KEY (CourseID)
);

/* This is the creation of the Courses table */

Create Table CourseEnrollments 
(
EnrollmentID INT NOT NULL,
  CONSTRAINT fk_Students
  FOREIGN KEY (StudentID)
  REFERENCES Students(StudentID)
CONSTRAINT fk_Faculty
  FOREIGN KEY (FacultyID)
  REFERENCES Faculty(FacultyID)
CONSTRAINT fk_Courses
  FOREIGN KEY (CourseID)
  REFERENCES Courses(CourseID)
CONSTRAINT enrollment_pk PRIMARY KEY (EnrollmentID)
);

Are you sure that column names written in quotes should have parenthesis without backslash? Please check it. That could be an issue, I am not sure. — uneq95
I'd VERY STRONGLY suggest you to consider recreating the COURSES table. Don't use double quotes, and don't put column explanation into its name. Are you aware of the fact that - every time you'd want to select something from that table - you'll have to write exactly select "Title (e.g Database security)" from COURSES >>> and it'll fail, because I didnt put a dot in "e.g." and didn't use capital "S" in "Security"? Such a table is a nightmare. There's a COMMENT you can use for any column, e.g. comment on column courses.title is 'e.g. database security'; — Littlefoot

Muhammad Waheed Muhammad Waheed · Accepted Answer · 2019-01-21T05:36:39

First of all a column which need to be constrained should be defined first explicitly then you can define constraint.Moreover, When you are defining more than one constraints then they need to be comma separated. Here is working code ( rest of the code is already fine).

Create Table CourseEnrollments 
(
EnrollmentID INT NOT NULL,
StudentID int,
FacultyID int,
CourseID int,
CONSTRAINT fk_Students
  FOREIGN KEY (StudentID)
  REFERENCES Students(StudentID),
CONSTRAINT fk_Faculty
  FOREIGN KEY (FacultyID)
  REFERENCES Faculty(FacultyID),
CONSTRAINT fk_Courses
  FOREIGN KEY (CourseID)
  REFERENCES Courses(CourseID),
CONSTRAINT enrollment_pk PRIMARY KEY (EnrollmentID)
);

SQL Foreign Key Help. ORA-00907 Missing right parenthesis

2 Answers