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?

[see this demo](http://sqlfiddle.com/#!2/a6e1f/1) – John Woo Jan 09 '13 at 08:23 — John Woo, Jan 09 '13 at 08:23

score 25 · Accepted Answer · answered Jan 09 '13 at 08:21

25

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.

answered Jan 09 '13 at 08:21

Corbin

33,060
6
68
78

Just to be sure if I've understood it correctly. There's no need for me to write FOREIGN KEY at all? Also is there any reason why the attribute Boss would reference Employee (where it's already declared)? – Bluening Jan 09 '13 at 08:29
1

@Bluening If you want a foreign key inlined in a CREATE statement, you must use the syntax as in your Example #2. `FOREIGN KEY` is not optional (at least not until MySQL honors ANSI SQL). – Corbin Jan 09 '13 at 08:29
2

@Bluening That's called a self-referential foreign key. It actually makes sense when you consider that a boss is still an employee. With self-referencing keys though, you must ALTER the table to add the key. Self-referencing ones cannot be added inline. – Corbin Jan 09 '13 at 08:32

Difference between using REFERENCES with and without FOREIGN KEY?

1 Answers1

Linked

Related