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?

`references` works for a single column and it works in most databases. — Gordon Linoff, May 28 '15 at 02:47

Gordon Linoff · Answer 1 · 2015-05-28T03:05:33.377

5

This is a bit long for a comment.

You use foreign key primarily in three circumstances:

You have a multi-key reference to another table.
You want to name the foreign key reference.
You want to add additional features, such as cascading deletes.

A fourth reason is also plausible: because local coding standards require the use of an explicit constraint.

edited May 28 '15 at 03:05

answered May 28 '15 at 02:52

Gordon Linoff

1,242,037
58
646
786

Thank you, I'll accept when I can. I would ask you to elaborate, but I don't think I'm at a level yet where the information would be useful. – m0meni May 28 '15 at 02:54
@Bohemian . . . Do you know which databases do not? I'm pretty sure Oracle, SQL Server, Postgres, and MySQL support inline references. – Gordon Linoff May 28 '15 at 03:05
@Gordon I had a quick rummage around google and all the databases I've ever used (a fair few) support inline, so I've deleted my comment. I think it's pretty safe to say "all" databases support the inline *references* clause on a column def. Still, it felt good finding that out. And have a +1 for your troubles :) – Bohemian May 28 '15 at 04:08
2

@Bohemian: MySQL does not support "inline" foreign keys. They are parsed but (silently!) ignored – May 28 '15 at 06:49
@a_horse_with_no_name didn't know that :) Oddly, I had a suspicion that they didn't work with mysql, but I just skimmed the doc and got a hit. They are just like `check` constraints then. Is there anything it *doesn't* ignore? :( – Bohemian May 28 '15 at 07:10

score 5 · Accepted Answer · answered May 28 '15 at 03:10

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

In PostgreSQL does a foreign key constraint only require REFERENCES?

2 Answers2