[lug] Q. on SQL Foreign Keys (PostgreSQL)

[Lee Woodworth]

On 08/28/2017 03:47 PM, stimits at comcast.net wrote:
> Nowhere can I find a table relating the foreign column to another table's column unless everything was single column to start with.

In postgresql anyway, in a foreign key constraint for a table, one column has a foreign
key relationship with exactly one other column even if the constraint has multiple
columns. Note that a table can have multiple foreign key constraints.

So you should only have a one-to-one mapping between columns per foreign-key constraint.

>From postgresql docs 9.5.7 section 5.3.5:

A foreign key can also constrain and reference a group of columns. As usual, it then needs
to be written in table constraint form. Here is a contrived syntax example:

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

Of course, the number and type of the constrained columns need to match the number and
type of the referenced columns.