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

user_201436 0
Viewed 2150

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?

postgresql sql
2 Answers

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.

user_64361910

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?

user_107268500
Related