In PostgreSQL does a foreign key constraint only require REFERENCES?

Question

I was reading the docs on PostgreSQL constraints because I wanted to see how to define foreign keys. In their examples

CREATE TABLE orders (
    order_id integer PRIMARY KEY,
    product_no integer REFERENCES products (product_no),
    quantity integer
);

I don't see FOREIGN KEY anywhere; however, in several other stack overflow questions (How to add "on delete cascade" constraints? for example) I have seen the FOREIGN KEY written. Is it necessary to write FOREIGN KEY or is it only necessary to use REFERENCES?

Answer 1

This is a good question.

You will notice FOREIGN KEY constraint in examples in doc related to DDL-constraints. I prefer to use FOREIGN KEY constraint as noted in Example 3 below.

You could do foreign key/references in different manners:

Parent table

CREATE TABLE products (
    product_no integer PRIMARY KEY,
    name text,
    price numeric
);

Child table - Ex1

Inline foreign key constraint without mentioning FOREIGN KEY

CREATE TABLE orders (
    order_id integer PRIMARY KEY,
    product_no integer REFERENCES products (product_no),
    quantity integer
);

OR

Child table - Ex2

Notice that parent and child table should have the same column name to use this concise notation.

CREATE TABLE orders (
    order_id integer PRIMARY KEY,
    product_no integer REFERENCES products,
    quantity integer
);

OR

Child table - Ex3

Notice that we are explicitly using FOREIGN KEY keyword here.

CREATE TABLE orders (
    order_id integer PRIMARY KEY,
    product_no integer,
    quantity integer,
    FOREIGN KEY (product_no) REFERENCES products (product_no),
);

In case when more than one fields need to be constrainted, FOREIGN KEY constraint can be written like this also:

CREATE TABLE t1 (
  a integer PRIMARY KEY,
  b integer,
  c integer,
  FOREIGN KEY (b, c) REFERENCES other_table (c1, c2)
);

These examples are taken from the docs.

SQL Fiddle Example: http://sqlfiddle.com/#!15/dd2d6