Does using REFERENCES without FOREIGN KEY still create a foreign key?

Question

I have done some SQL before but not loads. I am a bit confused about what REFERENCES does if you use it without FOREIGN KEY when creating your table e.g.

author_id INTEGER REFERENCES users(id),

As opposed to...

FOREIGN KEY (author_id) REFERENCES users(id)

If these are different what is the difference? And if they are the same which form should be preferred?

Answer 1

The first one:

author_id INTEGER REFERENCES users,

...is a shorthand of:

author_id INTEGER REFERENCES users (id),

...that is a shorthand of:

FOREIGN KEY (author_id) REFERENCES users (id)

...that in turn is a shorthand of the SQL standard:

CONSTRAINT <constraint-name> 
FOREIGN KEY (<column>) 
REFERENCES <table>(<columns>)

The first one covers the basic, most common case, and is succint. Good.

Now, the full syntax covers the general case with all possible variations. Consider for example:

• Composite foreign keys:

  book_id int not null,
  chapter_id int not null,
  constraint fk1 foreign key (book_id, chapter_id) 
    references chapter (book_id, chapter_id)
  

  Since it's a composite key, it cannot be specified at the column level, but at the table level.

• Forking foreign keys:

  owner_id int not null,
  constraint fk2 foreign key (owner_id) references person (id),
  constraint fk3 foreign key (owner_id) references company (id)
  

  In this case the same column points to multiple tables. Imagine this one combined with composite foreign keys...

In general, you'll see composite keys once in a while, and forking foreign keys very rarely. Most of the time you'll see simple foreign keys, and that's why the shorthand syntax is so useful.

Answer 2

If you use only REFERENCES PostgreSQL creates a foreign key:

postgres=# create table parent(id int primary key);
CREATE TABLE

postgres=# create table child(id int primary key, fk int references parent);
CREATE TABLE

postgres=# \d child;
               Table "public.child"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 id     | integer |           | not null | 
 fk     | integer |           |          | 
Indexes:
    "child_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
    "child_fk_fkey" FOREIGN KEY (fk) REFERENCES parent(id)

postgres=# 

From syntax point of view REFERENCES is used at column level whereas FOREIGN KEY is used at table level.

See What is difference between foreign key and reference key?