Wrong SQL Syntax; Incorrect syntax near ‘)’

I’m using Microsoft SQL Server Management Studio that’s hooked up to an Azure SQL database; usually, I’m used to using Oracle (It’s just a rookie mistake I guess). What exactly am I doing wrong? These are the error messages that I’m getting:

Msg 102, Level 15, State 1, Line 11
Incorrect syntax near ‘)’.

Msg 102, Level 15, State 1, Line 42
Incorrect syntax near ‘)’.

Msg 102, Level 15, State 1, Line 54
Incorrect syntax near ‘)’.

Msg 102, Level 15, State 1, Line 66
Incorrect syntax near ‘)’.

This is my code:

CREATE TABLE ElectiveCourses 
(
    ElectiveCourseID INT,
    ElectiveCoursePrefix CHAR(5),
    ElectiveCourseCode INT,
    ElectiveCreditHours INT,
    ElectiveCourseDescription VARCHAR(255),
    CatalogYearID INT,
    MajorID INT,
    PRIMARY KEY (ElectiveCourseID),
    FOREIGN KEY (CatalogYearID, MajorID)
);

CREATE TABLE Major 
(
    MajorID INT, 
    MajorTitle VARCHAR(50),
    MajorCode INT,
    MajorLevel CHAR(5),
    MajorDescription VARCHAR(255),
    PRIMARY KEY (MajorID)
);

CREATE TABLE CatalogYear 
(
    CatalogYearID INT,
    CatalogYear VARCHAR(10),
    CatalogYearStartTerm VARCHAR(10),
    CatalogYearEndTerm VARCHAR(10),
    PRIMARY KEY (CatalogYearID)
);

CREATE TABLE Student 
(
    StudentID INT,
    FirstName CHAR(25),
    LastName CHAR(10),
    MajorID INT,
    CatalogYearID INT,
    PhoneNumber VARCHAR(15),
    AreaCode INT,
    Address VARCHAR(50),
    State CHAR(50),
    PRIMARY KEY (StudentID),
    FOREIGN KEY (MajorID, CatalogYearID)
);

CREATE TABLE MajorCourses 
(
    MajorCourseID INT,
    MajorCoursePrefix CHAR(5),
    MajorCourseCode INT,
    MajorCreditHours INT,
    MajorCourseDescription VARCHAR(255),
    CatalogYearID INT,
    MajorID INT,
    PRIMARY KEY (MajorCourseID),
    FOREIGN KEY (CatalogYearID, MajorID)
);

CREATE TABLE Courses   
(
    CourseID INT,
    CoursePrefix CHAR (5),
    CourseCode INT,
    CreditHours INT,
    CourseDescription VARCHAR(255),
    CatalogYearID INT,
    MajorID INT,
    PRIMARY KEY (CourseID),
    FOREIGN KEY (CatalogYearID, MajorID)
);

Answer

WHAT other table/column(s) do these foreign key reference?? You need to provide that info!

CREATE TABLE ElectiveCourses 
(
    .... all the columns ......
    PRIMARY KEY (ElectiveCourseID),
    FOREIGN KEY (CatalogYearID, MajorID) **REFERENCES dbo.OtherTable(col1, col2)**
);

Also, those references have to match a whole, complete primary key of another table – you cannot reference only parts of a (composite) primary key. The names, order, and datatype of the columns in the foreign key must exactly match the primary key of the table being referenced.

I didn’t find any other table in your sample shown that has a primary key that’s made up from (CatalogYearID, MajorID) – so this reference here (and some of the others, too) is very unclear …..

And as a side note: I would strongly recommend to explicitly name your constraints:

CREATE TABLE dbo.ElectiveCourses 
(
    .... all the columns ......
    CONSTRAINT PK_ElectiveCourses        
        PRIMARY KEY CLUSTERED (ElectiveCourseID),
    CONSTRAINT FK_ElectiveCourses_OtherTable
        FOREIGN KEY (CatalogYearID, MajorID) 
        REFERENCES dbo.OtherTable(col1, col2)
);

That makes it just soooo much easier to reference those constraints if you ever need to e.g. disable or drop them.