Can FOREIGN KEY be omitted in PostgreSQL when using REFERENCES?

Question

I'm wondering if there's any (maybe subtle) difference between these two SQL statements:

CREATE TABLE profiles (
    profile_id SERIAL PRIMARY KEY NOT NULL,
    bio TEXT,
    user_id INTEGER NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

and

CREATE TABLE profiles (
    profile_id SERIAL PRIMARY KEY NOT NULL,
    bio TEXT,
    user_id INTEGER NOT NULL REFERENCES users(user_id) 
);

I've noticed that when I create a table in Postico with the first notation, but look at the DDL of the created profiles table later, the FOREIGN KEY is removed and I end up with the shorter second notation.

Create table with FOREIGN KEY:

DDL view doesn't show FOREIGN KEY:

So, I'm wondering (and seeking confirmation) that the two statements are in fact 100% equivalent or if there are some subtle differences in what they do to the DB.

Any pointer to official resources (and maybe also how that differs from MySQL) would be appreciated.

Answer 1

The two samples you show do the same thing, just with a different syntax.

The first method is called table constraint, the second column constraint, but the latter name is somewhat misleading because the constraint is on the table as well.

The main difference is that the column constraint syntax is shorter, but cannot be used for all constraints: if you have for example a primary key that contains two columns, you have to write it in the table constraint syntax.

Answer 2

In PostgreSQL, you define a foreign key through a foreign key constraint. A foreign key constraint indicates that values in a column or a group of columns in the child table match with the values in a column or a group of columns of the parent table. We say that a foreign key constraint maintains referential integrity between child and parent tables.

This may explain to you better or you can read about Foreign Keys documentation .

Answer 3

DDL view doesn't show FOREIGN KEY

DDL view created by unknown third-party tool in not an argument.

See fiddle. Foreign key exists in both cases. Moreover, I do not see the result difference for both DDL queries.

PS. As a recommendation - always specify the constraint name explicitly. What if you need to delete it? It is problematic without the constraint name...