CODEWITHSUNDEEP
mysqldatabasedatabase-design
Shuzheng
Shuzheng

Reputation: 13850

MySQL: `REFERENCES` vs `FOREIGN KEY` + `REFERENCES`

MySQL: REFERENCES vs FOREIGN KEY + REFERENCES

I thought that REFERENCESwas some sort of shorthand syntax for the more verbose FOREIGN KEY REFERENCES syntax.

However, the below table is not acting like it is constrained by a foreign key constraint. I can easily add a tuple with a courseid not in the Courses relation.

Can someone clarify this issue?

CREATE TABLE `Exam`(
    `courseid` VARCHAR(20) REFERENCES `Courses`(courseid) ON DELETE RESTRICT ON UPDATE CASCADE,
    `examno` INT(1) DEFAULT 1 CHECK(`examno` >= 1),
    `weight` FLOAT(5) DEFAULT 1 CHECK(`weight` > 0),
    PRIMARY KEY(courseId, examNo)
);

Upvotes: 1

Views: 842

Answers (1)

Rob MacMillian
Rob MacMillian

Reputation: 576

What version of mysql are you using?

What do you get when you run this?

show create table Exam;

What I get is:

     CREATE TABLE `Exam` (
     `courseid` varchar(20) NOT NULL DEFAULT '',
     `examno` int(1) NOT NULL DEFAULT '1',
      `weight` float DEFAULT '1',
      PRIMARY KEY (`courseid`,`examno`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

which means that MySQL is ignoring the "REFERENCES" portion of your create table statement. I think that you do need the full statement like:

FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE CASCADE

Upvotes: 1

Related Questions