SQL Missing left/right parenthesis

yshaikh20x

I keep getting a missing right or left parenthesis error when I try to create my tables with a foreign key, but when I remove the foreign key my code has no problem creating the tables. I need the code to run with the foreign keys.

--deletes respective tables previously created
drop table DEPARTMENT;
drop table POSITION;
drop table EMPLOYEE;
drop table COMPANY;
drop table DEGREE;
drop table GRAD_INFO;
drop table STUDENT;

CREATE TABLE Department
(
    Dept_Name varchar(255) PRIMARY KEY, --primary key
    Dept_Phone_Num numeric(35) not null
);

CREATE TABLE Position
( 
    Position_Name varchar(255) NOT NULL PRIMARY KEY, --primary key
    Position_Salary decimal(10, 2) not null, 
    Employee_num numeric(35) not null,

    CONSTRAINT fk_Dept_Name 
        FOREIGN KEY Dept_Name REFERENCES Department (Dept_Name) --foreign key 
);

CREATE TABLE Employee
(
    EmployeeID INT NOT NULL PRIMARY KEY, --primary key
    Employee_Salary DECIMAL(10,2) DEFAULT NULL,
    Employee_FName VARCHAR(30) DEFAULT NULL,
    Employee_LName VARCHAR(30) NOT NULL,
    Employee_Address VARCHAR(30) DEFAULT NULL,
    Employee_Phone_Num INT(10) DEFAULT NULL,
    Employee_Email VARCHAR(30) NOT NULL, 
    Employee_Status VARCHAR(10) NOT NULL,

    CONSTRAINT Position_Name 
        FOREIGN KEY Position_Name REFERENCES Position(Position_Name), --foreign key
    CONSTRAINT fk_Dept_Name 
        FOREIGN KEY Dept_Name REFERENCES Department(Dept_Name) --foreign key
);

CREATE TABLE Company
(
    Comp_Name VARCHAR(20) NOT NULL PRIMARY KEY, --primary key 
    Position_Offer VARCHAR(30) NOT NULL,
    Salary_Offer DECIMAL(10,2) NOT NULL,
    Signing_Bonus DECIMAL(10,2) DEFAULT NULL,
    Comp_Phone_Num INT(10) NOT NULL,
    Comp_Address VARCHAR(30) NOT NULL,

    CONSTRAINT fk_Employee_ID 
        FOREIGN KEY Employee_ID REFERENCES Employee (Employee_ID), --foreign key
    CONSTRAINT fk_Dept_Name 
        FOREIGN KEY Dept_Name REFERENCES Department (Dept_Name) --foreign key 
);

CREATE TABLE Degree
(
    Degree_Type varchar(255) PRIMARY KEY, --primary key
    Academic_Status varchar(255) not null, 
    Academic_Level varchar(255) not null, 
    Major varchar(255) not null, 
    Minor varchar(255) default null
);

CREATE TABLE Gead_Info
(
    Grad_Status VARCHAR(255) NOT NULL PRIMARY KEY, --primary key
    College_Name VARCHAR(255) NOT NULL,  
    Num_Of_Degrees NUMERIC(10) NOT NULL,  
    Grad_Date NUMERIC(6) NOT NULL,

    CONSTRAINT fk_Degree_Type 
        FOREIGN KEY Degree_Type REFERENCES Degree(Degree_Type) --foreign key
);

CREATE TABLE Student
( 
    Student_ID NUMERIC(10) NOT NULL PRIMARY KEY, --primary key 
    Student_FName VARCHAR(20) NOT NULL, 
    Student_LName VARCHAR(20) NOT NULL, 
    Student_Address VARCHAR(20) NOT NULL, 
    Student_Email VARCHAR(20) NOT NULL, 
    Student_Phone_Num VARCHAR(10) NOT NULL,

    CONSTRAINT fk_Comp_Name 
        FOREIGN KEY Comp_Name REFERENCES Company(Comp_Name), --foreign key
    CONSTRAINT fk_Grad_Status 
        FOREIGN KEY Grad_Status REFERENCES Gead_Info(Grad_Status) --foreign key
);

The degree table is the only table where I don't get an error.

Should I use an alter table instead after I create all the tables or is there another way to solve my problem?

edit: updated code

Bill Hall

I think your most immediate issue is with the Department table not having a comma between the two CONSTRAINT clauses. You might also run into issues with your Position table's CONSTRAINT clause referencing the Position table instead of the Employee table, although honestly I don't think you need that relationship defined at all since the Employee table already has a foreign key constraint with Position. And like what was stated earlier, your table definitions aren't in the right order so you have some tables referencing other tables before those others are created.

Give this a shot to see if the syntax works. I've added the column definition for the foreign key in Position, put parentheses around the foreign key column name in the CONSTRAINT clause, and removed the space between Department and the left paren in the CONSTRAINT clause. If this script works, make the same changes to your other table defs.

CREATE TABLE Department(
Dept_Name varchar(255) Primary Key, --primary key
Dept_Phone_Num numeric(35) not null
);

CREATE TABLE Position( 
Position_Name varchar(255) not null Primary Key, --primary key
Position_Salary decimal(10, 2) not null, 
Employee_num numeric(35) not null,
Dept_Name varchar(255) not null,
CONSTRAINT fk_Dept_Name FOREIGN KEY (Dept_Name) REFERENCES Department(Dept_Name) --foreign key 
);

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related