What is the difference between REFERENCES with, or without a FOREIGN KEY

Question

In regards to SQLite, What is the difference between REFERENCES with, or without a FOREIGN KEY?

What is the difference between this

CREATE TABLE players_set ( 
    _id            INTEGER PRIMARY KEY AUTOINCREMENT
                           NOT NULL,
    player_id      INTEGER REFERENCES players ( _id ) ON DELETE CASCADE,
    players_set_id INTEGER REFERENCES players_set_names ( _id ) 
);

and this:

CREATE TABLE players_set ( 
    _id            INTEGER PRIMARY KEY AUTOINCREMENT
                           NOT NULL,
    player_id INTEGER,
    players_set_id INTEGER REFERENCES players_set_names ( _id ),
    FOREIGN KEY (player_id) REFERENCES players ( _id ) ON DELETE CASCADE        
);

I found this other question: Difference between using REFERENCES with and without FOREIGN KEY?

I read the documentation, it didn't make it clear for me though.

To be precise, I'm using SQLite on Android (hence the tag). I don't know if that makes any difference, but if it has its own quirks, it would be very useful for me to find out about.

If you ever have to use MySQL, don't use the first version: MySQL will ignore the foreign key definition (i.e. it will not be created) but will not tell you. — , Feb 08 '14 at 16:13

score 7 · Accepted Answer · answered Feb 08 '14 at 15:58

The FOREIGN KEY syntax is more flexible than defining it inline in the column definition (e.g., it allows you to define a composite foreign key, where the combination of two or more fields should exist in the referencing columns).

In your case, there is no difference between the two DDL statements. One could say that the inline definition of foreign keys is nothing more than syntactic sugaring.

What is the difference between REFERENCES with, or without a FOREIGN KEY

1 Answers1

Linked