Konrad Morawski Konrad Morawski - 5 months ago 8
SQL Question

Difference between REFERENCES with or without FOREIGN KEY

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

But it regards

MySQL
, whereas I'm asking about
SQLite
.

The essence of the question remains the same: what is the difference between (say):

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

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 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 not, the tag is irrelevant of course.

Answer

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.

Comments