StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p><strong>The reason foreign key constraints exist is to guarantee that the referenced rows exist.</strong></p> <p>"The foreign key identifies a column or a set of columns in one table that refers to a column or set of columns in another table. The values in one row of the referencing columns must occur in a single row in the referenced table. </p> <p>Thus, a row in the referencing table cannot contain values that don't exist in the referenced table (except potentially NULL). This way references can be made to link information together and it is an essential part of database normalization." (<a href="http://en.wikipedia.org/w/index.php?title=Foreign_key&oldid=327869194" rel="nofollow noreferrer">Wikipedia</a>)</p> <hr> <p><strong>RE: Your question: "I can't imagine the need to join tables by fields that aren't FKs":</strong></p> <p>When defining a Foreign Key constraint, the column(s) in the referencing table must be the primary key of the referenced table, or at least a candidate key.</p> <p>When doing joins, there is no need to join with primary keys or candidate keys.</p> <p>The following is an example that could make sense:</p> <pre><code>CREATE TABLE clients ( client_id uniqueidentifier NOT NULL, client_name nvarchar(250) NOT NULL, client_country char(2) NOT NULL ); CREATE TABLE suppliers ( supplier_id uniqueidentifier NOT NULL, supplier_name nvarchar(250) NOT NULL, supplier_country char(2) NOT NULL ); </code></pre> <p>And then query as follows:</p> <pre><code>SELECT client_name, supplier_name, client_country FROM clients INNER JOIN suppliers ON (clients.client_country = suppliers.supplier_country) ORDER BY client_country; </code></pre> <p>Another case where these joins make sense is in databases that offer geospatial features, like SQL Server 2008 or Postgres with PostGIS. You will be able to do queries like these:</p> <pre><code>SELECT state, electorate FROM electorates INNER JOIN postcodes on (postcodes.Location.STIntersects(electorates.Location) = 1); </code></pre> <p>Source: <a href="http://conceptdev.blogspot.com/2007/12/sql-server-2008-geography-stintersects.html" rel="nofollow noreferrer">ConceptDev - SQL Server 2008 Geography: STIntersects, STArea</a></p> <p>You can see another similar geospatial example in the accepted answer to the post "<a href="https://stackoverflow.com/questions/303927/">Sql 2008 query problem - which LatLong’s exists in a geography polygon?</a>":</p> <pre><code>SELECT G.Name, COUNT(CL.Id) FROM GeoShapes G INNER JOIN CrimeLocations CL ON G.ShapeFile.STIntersects(CL.LatLong) = 1 GROUP BY G.Name; </code></pre> <p>These are all valid SQL joins that have nothing to do with foreign keys and candidate keys, and can still be useful in practice.</p>

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