b.le b.le - 1 month ago 20
MySQL Question

Incorrect syntax near the keyword 'CONSTRAINT'

I am getting this error message ...


Msg 156, Level 15, State 1, Line 20

Incorrect syntax near the keyword 'CONSTRAINT'.


I've done my research and I'm still stuck on completing this script.

Here`s what I entered.

DROP TABLE SEMESTER;
DROP TABLE CLASS;
DROP TABLE STUDENT;

CREATE TABLE STUDENT (
stuid int not null,
stulname CHAR(40) not null,
stufname CHAR(40) not null,
stugender CHAR(1) not null,
stubirthdate DATE not null);

CREATE TABLE CLASS (
title CHAR(40) not null PRIMARY KEY,
instructor CHAR(40) not null );

CREATE TABLE SEMESTER (
year int not null );


CONSTRAINT pk_student PRIMARY KEY (stuid, stulname, stufname, stugender, stubirthdate),
CONSTRAINT pk_class PRIMARY KEY (title, instructor),
CONSTRAINT pk_semester PRIMARY KEY (semid, year));

Answer

I think those CONSTRAINT statements need to be inside the CREATE TABLE statements. For example, the STUDENT table should look like this:

CREATE TABLE STUDENT (
    stuid int not null,
    stulname CHAR(40) not null,
    stufname CHAR(40) not null,
    stugender CHAR(1) not null,
    stubirthdate DATE not null,
    CONSTRAINT pk_student PRIMARY KEY (stuid, stulname, stufname, stugender, stubirthdate), 
);

Otherwise, how could the RDBMS know to which table each constraint actually belongs?