CODEWITHSUNDEEP
mysqlsqlpostgresql
cardamom
cardamom

Reputation: 7421

syntax differences MySQL / PostgresSQL for foreign keys

I have used many databases that other people built with foreign keys but have only recently been learning how to include them myself.

Can see from this answer that the following two statements with and without 'FOREIGN KEY' are equivalent:

author_id INTEGER REFERENCES author(id)

author_id INTEGER,
FOREIGN KEY(author_id) REFERENCES author(id)

Then the following runs perfectly on Postgres but produces an error in MySQL:

CREATE TABLE cities (
        city     varchar(80) primary key,
        location point
);

CREATE TABLE weather (
        city      varchar(80) references cities(city),
        temp_lo   int,
        temp_hi   int,
        prcp      real,
        date      date
);

(tested here - https://dbfiddle.uk/)

What are the differences in the syntax for this between the two dialects?

Upvotes: 0

Views: 206

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

There are multiple ways to express a foreign key relationship in a create table statement. Here are some ways:

Inline with the column definition:

city      varchar(80) references cities(city),

Explicitly as a foreign key:

city      varchar(80),
. . .
foreign key (city) references cities(city),

Explicitly as a constraint:

city      varchar(80),
. . .
constraint fk_weather_city foreign key (city) references cities(city),

MySQL does not support the first version, with inlined constraints. Most if not all other databases do.

The first version only handles foreign keys with a single column reference, so it is not as general as the other methods (and the syntax allows for naming constraints and for the foreign key keyword for the inline foreign key definitions).

The explicit definition is more general (handling multiple columns). I tend to prefer explicitly named constraints. I do admit to sometimes using the inline version, simply because it is more convenient.

Upvotes: 3

Related Questions