CODEWITHSUNDEEP
mysqlsql
user3763833
user3763833

Reputation:

I don't know where my error is at 'incorrect syntax near the keyword CONSTRAINT'

CREATE TABLE ClassRegistration(
StudentID int   NOT NULL,
CourseID int    NOT NULL,

CONSTRAINT PK_ClassRegistration PRIMARY KEY (StudentID, CourseID),
CONSTRAINT FK_ClassRegistration_Students,
FOREIGN KEY(StudentID) REFERENCES Students(ID),
ON DELETE CASCADE ON UPDATE CASCADE,

CONSTRAINT FK_ClassRegistration_Classes,
FOREIGN KEY(CourseID) REFERENCES Course(ClassNumber),
ON DELETE CASCADE ON UPDATE CASCADE
)

I believe it is simply an issue of where to put commas but I'm teaching myself and so it's hard to figure this out.

Upvotes: 0

Views: 7505

Answers (3)

user330315
user330315

Reputation:

The constraint definition does not have "embedded" commas:

CREATE TABLE ClassRegistration
(
   StudentID int   NOT NULL,
   CourseID int    NOT NULL,

   CONSTRAINT PK_ClassRegistration PRIMARY KEY (StudentID, CourseID),

   CONSTRAINT FK_ClassRegistration_Students   --- no comma here
   FOREIGN KEY(StudentID) REFERENCES Students(ID) -- no comma here
   ON DELETE CASCADE ON UPDATE CASCADE,

   CONSTRAINT FK_ClassRegistration_Classes -- no comma here
   FOREIGN KEY(CourseID) REFERENCES Course(ClassNumber) -- no comma here
   ON DELETE CASCADE ON UPDATE CASCADE
);

Btw: the part CONSTRAINT PK_ClassRegistration is useless. You can't give the primary key constraint a name in MySQL (you can specify it, but it is ignored and always named PRIMARY)

Upvotes: 2

potashin
potashin

Reputation: 44581

CREATE TABLE ClassRegistration(
    StudentID int   NOT NULL,
    CourseID int    NOT NULL,

    PRIMARY KEY (StudentID, CourseID),

    CONSTRAINT FK_ClassRegistration_Classes
    FOREIGN KEY(StudentID) REFERENCES Students(ID) ON DELETE CASCADE ON UPDATE CASCADE,

    CONSTRAINT FK_ClassRegistration_Classes
    FOREIGN KEY(CourseID) REFERENCES Course(ClassNumber) ON DELETE CASCADE ON UPDATE CASCADE
)

Upvotes: 1

Jean-François Savard
Jean-François Savard

Reputation: 21004

You add way too much comma,

CREATE TABLE ClassRegistration(
StudentID int   NOT NULL,
CourseID int    NOT NULL,

CONSTRAINT PK_ClassRegistration PRIMARY KEY (StudentID, CourseID),
CONSTRAINT FK_ClassRegistration_Students
FOREIGN KEY(StudentID) REFERENCES Students(ID)
ON DELETE CASCADE ON UPDATE CASCADE,

CONSTRAINT FK_ClassRegistration_Classes
FOREIGN KEY(CourseID) REFERENCES Course(ClassNumber)
ON DELETE CASCADE ON UPDATE CASCADE
);

See this for information on correct syntax.

http://dev.mysql.com/doc/refman/5.1/en/create-table.html

Upvotes: 1

Related Questions