Why Are Foreign Keys More Used in Theory Than in Practice

Why are foreign keys more used in theory than in practice?

The reason foreign key constraints exist is to guarantee that the referenced rows exist.

"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.

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." (Wikipedia)

---

RE: Your question: "I can't imagine the need to join tables by fields that aren't FKs":

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.

When doing joins, there is no need to join with primary keys or candidate keys.

The following is an example that could make sense:

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
);

And then query as follows:

SELECT 
    client_name, supplier_name, client_country 
FROM 
    clients 
INNER JOIN
    suppliers ON (clients.client_country = suppliers.supplier_country)
ORDER BY
    client_country;

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:

SELECT
    state, electorate 
FROM 
    electorates 
INNER JOIN 
    postcodes on (postcodes.Location.STIntersects(electorates.Location) = 1);

Source: ConceptDev - SQL Server 2008 Geography: STIntersects, STArea

You can see another similar geospatial example in the accepted answer to the post "Sql 2008 query problem - which LatLong’s exists in a geography polygon?":

SELECT 
    G.Name, COUNT(CL.Id)
FROM
    GeoShapes G
INNER JOIN 
    CrimeLocations CL ON G.ShapeFile.STIntersects(CL.LatLong) = 1
GROUP BY 
    G.Name;

These are all valid SQL joins that have nothing to do with foreign keys and candidate keys, and can still be useful in practice.

Why use Foreign Key constraints in MySQL?

Foreign keys enforce referential integrity. These constraints guarantee that a row in a table order_details with a field order_id referencing an orders table will never have an order_id value that doesn't exist in the orders table.

Foreign keys aren't required to have a working relational database (in fact MySQL's default storage engine doesn't support FKs), but they are definitely essential to avoid broken relationships and orphan rows (ie. referential integrity). The ability to enforce referential integrity at the database level is required for the C in ACID to stand.

As for your concerns regarding performance, in general there's a performance cost, but will probably be negligible. I suggest putting in all your foreign key constraints, and only experiment without them if you have real performance issues that you cannot solve otherwise.

what are the advantages of defining a foreign key

Foreign keys with constraints(in some DB engines) give you data integrity on the low level(level of database).
It means you can't physically create a record that doesn't fulfill relation.
It's just a way to be more safe.

Why is a primary-foreign key relation required when we can join without it?

The main reason for primary and foreign keys is to enforce data consistency.

A primary key enforces the consistency of uniqueness of values over one or more columns. If an ID column has a primary key then it is impossible to have two rows with the same ID value. Without that primary key, many rows could have the same ID value and you wouldn't be able to distinguish between them based on the ID value alone.

A foreign key enforces the consistency of data that points elsewhere. It ensures that the data which is pointed to actually exists. In a typical parent-child relationship, a foreign key ensures that every child always points at a parent and that the parent actually exists. Without the foreign key you could have "orphaned" children that point at a parent that doesn't exist.

Why is it necessary to explicitly specify foreign keys and references in databases?

It is not necessary to specify foreign key relationships. It is just a good idea.

When you specify the relationship, the database ensures relational integrity. That is, it ensures that the values in the foreign key column are legitimate values.

In addition, the cascade options on foreign keys are a big help when values are updated or deleted.

SQL and storing foreign keys on multiple tables vs joining through tables for queries

How much data do you expect to store in your database? If there are relatively small amount of rows in each table for the joins the execution time between a normalized and non-normalized database will be negligible.

So instead just go with a design that makes sense to you and you can normalize and de-normalize tables where it makes sense.

Here's a good read on the subject:http://www.codinghorror.com/blog/2008/07/maybe-normalizing-isnt-normal.html

Should I really use foreign keys?

"All related tables" is not always clear, so it's not an obvious situation. There can be tables that have a common column but may never see each other.

It's handy, however, to prevent errors that slip past your primary defenses and allow data to be entered that's not easily traceable.

They don't help make queries more efficient if you have the proper indexes in place, and a good application will filter input enough that they should never be needed. But mistakes happen, and they are a cheap line of defense.

If you're just getting comfortable with designing databases, they aren't something to spend a lot of time worrying about and refining in great detail, once you have the basic parent/child relationships in place.

Here's some background--

What's wrong with foreign keys?

---

Related Topics