mysql - Difference between using REFERENCES with and without FOREIGN KEY?

Question

Basically I'd like to know the difference between using REFERENCES with or without a foreign key.

I have these 2 examples:

CREATE TABLE Employee 
(
     id        INT,
     name      VARCHAR(50),
     birthYear INT,
     boss      INT REFERENCES Employees(id),
     worksAt   INT NOT NULL REFERENCES Department(id) ON DELETE CASCADE,
     PRIMARY KEY (id,worksAt)
);

Example 2:

CREATE TABLE Department 
(
      id                INT PRIMARY KEY,
      name              VARCHAR(50),
      numberOfEmployees INT,
      location          INT NOT NULL,
      country           INT NOT NULL,
      manager           INT,
      FOREIGN KEY (location,country) REFERENCES Location(locId,countryId),
      UNIQUE KEY (manager)
);

What I'm asking here is why does the second example use the FOREIGN KEY keyword while the first one merely uses REFERENCES.

Also, the first one seems to reference itself (I think the s in Employees is a mistake) If so, why does it use REFERENCES if it's referencing itself?

Answer 1

Congratulations! You've stumbled upon one of the weirder quirks of MySQL. The first syntax does absolutely nothing. It's silently, yes, silently ignored.

Furthermore, InnoDB does not recognize or support “inline REFERENCES specifications” (as defined in the SQL standard) where the references are defined as part of the column specification. InnoDB accepts REFERENCES clauses only when specified as part of a separate FOREIGN KEY specification. For other storage engines, MySQL Server parses and ignores foreign key specifications.

It's a little under halfway down the create table documentation.