What does `REFERENCES` do in sql?

Question

I'm trying to write a 1-many schema with simple example. I have one table books mapping to many tags. (see fiddle links below)

What I want is for the tags table to have a foreign key to books table. I think I have managed this:

 
CREATE TABLE IF NOT EXISTS `books` (
  `id` int(6) unsigned NOT NULL,
  `book_name` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;
 
 
CREATE TABLE IF NOT EXISTS `tags` (
  `tag_id` int(6) unsigned NOT NULL,
  `tag` varchar(100) NOT NULL,
  `book_id` int(6)  unsigned NOT NULL,
  PRIMARY KEY (`tag_id`)
);

I don't understand the difference between:

  `book_id` int(6)  unsigned NOT NULL,

and

  `book_id` int(6)  unsigned NOT NULL REFERENCES `books`(`id`),

Both compile fine and work with inner joins

http://sqlfiddle.com/#!9/612337/1

http://sqlfiddle.com/#!9/2043b8/3

So what's the difference between the snippets?

so the correcy way is with explicit foreign key like so: http://sqlfiddle.com/#!9/f25b99/2 ? — nz_21, Mar 06 '22 at 17:30
Yup according to that answer. Upvoted your question because it made me discover this weird mysql behaviour. — Zakaria, Mar 06 '22 at 17:31

Bill Karwin · Accepted Answer · 2022-03-06T20:42:58.397

https://dev.mysql.com/doc/refman/8.0/en/ansi-diff-foreign-keys.html says:

MySQL parses but ignores “inline REFERENCES specifications” (as defined in the SQL standard) where the references are defined as part of the column specification. MySQL accepts REFERENCES clauses only when specified as part of a separate FOREIGN KEY specification.

This syntax creates a column; it does not create any sort of index or key.

When used in this fashion, the REFERENCES clause is not displayed in the output of SHOW CREATE TABLE or DESCRIBE:

That is, the following creates the column book_id but not a foreign key constraint:

`book_id` int unsigned NOT NULL REFERENCES `books`(`id`),

You must do it this way:

`book_id` int unsigned NOT NULL,
FOREIGN KEY (book_id) REFERENCES `books`(`id`),

The decision was made years ago to skip implementing inline REFERENCES as a column option. You would have to use the separate line for the FOREIGN KEY constraint anyway if you made a foreign key for two or more columns. The inline REFERENCES as part of a column definition is just a convenient short-cut syntax.

The fact that the syntax is parsed but ignored has been reported as a bug since at least 2004, but never fixed:

But because of MySQL pluggable storage engine architecture, it's difficult to fix this bug. SQL syntax parsing is handled independently of the storage engine, but implementation of constraints is handled within the storage engine. The SQL parser must allow the syntax, because it doesn't know whether the feature will be ignored or supported by a given storage engine.

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=b4d75d46dd49feedcd59107dd3eb9545 — Akina, Mar 06 '22 at 18:30
thanks @Bill for the great answer. Is there anyway performance benefit between using Foreign Key vs Referenve? Of course, the former does some validation - but will my queries actually work faster if I use it? — nz_21, Mar 08 '22 at 21:41
Depends on the query. A foreign key implicitly creates an index if one does not exist, which will help some queries. You'll have to test it yourself using your queries, there's no way I can guess what your queries are. — Bill Karwin, Mar 08 '22 at 21:47

What does `REFERENCES` do in sql?

1 Answers1