Difference between references and foreign key

Question

here are two declaration in sql

create table bookAdoption ( courseId int, sem int, isbn int, PRIMARY KEY(courseId, sem, isbn), FOREIGN KEY(courseId) REFERENCES course(courseId), FOREIGN KEY(sem) REFERENCES enroll(sem) );

and the other one

CREATE TABLE bookAdoption ( courseId INT REFERENCES course(courseId) , sem INT REFERENCES enroll(sem), isbn INT REFERENCES text1(isbn), PRIMARY KEY(courseId, sem, isbn) );

what is the between using only references keyword

and using both references and foreign keyword both together screenshot

According to the grammar, only using references keyword like that is invalid syntax — Esailija
– Esailija, Commented Feb 3, 2013 at 13:17
@tiger When I run the second example verbatim, I get the expected syntax error: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTREFERENCES course(courseId) , sem INT REFERENCES enroll(sem), isbn ' at line 3 — Esailija
– Esailija, Commented Feb 3, 2013 at 13:19
@Esailija spelling mistake it should be references instead of intreferences now try — tiger
– tiger, Commented Feb 3, 2013 at 13:22
@Esailija: it is ignored (which is documented in the manual) No foreign key constraint will be created and you will be able to insert values that don't exist in the referenced table. See here: sqlfiddle.com/#!2/725388/2 — user330315
– user330315, Commented Feb 3, 2013 at 13:31

Mahmoud Gamal · Accepted Answer · 2013-02-03 13:19:32Z

You can't use the REFERENCES without a foreign key constraint, as quoted from the MySQL Documentation, FOREIGN KEY Constraints:

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.

Bogdan Gavril MSFT · Accepted Answer · 2013-02-03 13:21:46Z

A FOREIGN KEY can only reference a column that is UNIQUE.

In practice we ussualy add foreign key constraints to primary keys, and we say that the FK references the PK. But it is possible to add such a constraint with any UNIQUE column(s).

In order for this to work SQL syntax requires you to:

declare a foreign key
tell it what to reference

mohammadAli · Accepted Answer · 2022-10-01 02:54:24Z

What's the difference between foreign key constraint and referencing a column?

The difference is simple - column referencing is parsed but ignored.

See fiddle - when column referencing is used then neither index nor constraint / foreign key are created.

Collectives™ on Stack Overflow

Difference between references and foreign key

3 Answers 3

Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Linked

Related