StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Short answer: Multi-column foreign keys naturally refer to multi column primary keys. There can still be an autogenerated id column that is part of the primary key.</p> <p>Philosophical answer: <strong>Primary key is the identity of the row</strong>. If there there is a bit of information that is an intrinsic part of the identity of the row (such as which customer the article belongs to.. in a multi customer wiki) - The information should be part of the primary key.</p> <p>An example: System for organizing LAN parties</p> <p>The system supports several LAN parties with the same people and organizers attending thus:</p> <pre><code>CREATE TABLE users ( users_id serial PRIMARY KEY, ... ); </code></pre> <p>And there are several parties:</p> <pre><code>CREATE TABLE parties ( parties_id serial PRIMARY KEY, ... ); </code></pre> <p>But most of the other stuff needs to carry the information about which party it is linked to:</p> <pre><code>CREATE TABLE ticket_types ( ticket_types_id serial, parties_id integer REFERENCES parties, name text, .... PRIMARY KEY(ticket_types_id, parties_id) ); </code></pre> <p>...this is because we want to <strong>refer to primary keys</strong>. Foreign key on table <strong>attendances</strong> points to table <strong>ticket_types</strong>.</p> <pre><code>CREATE TABLE attendances ( attendances_id serial, parties_id integer REFERENCES parties, ticket_types_id integer, PRIMARY KEY (attendances_id, parties_id), FOREIGN KEY (ticket_types_id, parties_id) REFERENCES parties ); </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload