Dev Learning Lab

The labs on this site need JavaScript to run. The same content is written out below.

Fundamentals › Database

Foreign keys

What is a foreign key, and what changes without one?

The member_id column of the orders table holds "the number of the member who placed this order." When you write the line FOREIGN KEY (member_id) REFERENCES member(id) for this column, it becomes a foreign key. It is a promise to the database that the value in member_id must be an id that really exists in the member table, so the database rejects any INSERT or DELETE that breaks the promise. In this lab you remove that line, or change the member_id of the second order, and run the same SQL each time. You see whether the database accepts each statement and whether an order that points to a missing member is left at the end.

Checked against real execution · Ran all 6 combinations on H2 2.4.240 and checked against the results, and checked the rules against the MySQL documentation

Concepts in this lab

Primary key, unique key, foreign key
A primary key (PK) is the column that uniquely identifies each row of a table. Its values cannot be duplicated or empty, and a table has only one. A unique key is a unique constraint, which prevents duplicate values, and a table can have several. A foreign key (FK) is a column that references the primary key (or a unique key) of another table. It guarantees that the referenced row really exists.
Constraint
This is a rule about the values that can go into a table, written in the definition of the table. A primary key, a unique constraint, NOT NULL, CHECK, and a foreign key are typical examples. When an INSERT, UPDATE, or DELETE breaks a rule, the database rejects that statement with an error, and the table does not change. If you check only in the application code, wrong values can get in through code that skips the check or through SQL that someone runs directly, but a constraint blocks a value in the same way whatever route it comes through. Without constraints, wrong values are saved without an error, and the problem only becomes visible when you query the data later.
Referential integrity
This is the rule that a value pointing to a row in another table must point to a row that really exists. For example, if the member_id of an order is 3, the member table must have a member with id 3, and that member must not be deleted while the order remains. When you declare a foreign key, the database checks this rule at every statement. It rejects an INSERT that puts in a missing value, and it rejects a DELETE of a parent row that still has rows pointing to it. Without a foreign key, the database does not notice an order that points to a missing row, so the application has to check every time, or has to find the mismatched data later. A row whose foreign key column is empty (NULL) says that it points to nothing, so most databases, including MySQL InnoDB, do not treat it as breaking this rule.
Parent table and child table
Of two tables joined by a foreign key, the table that holds the pointing value is the child table, and the table that is pointed to is the parent table. If member_id in orders points to id in member, orders is the child and member is the parent. A foreign key is defined on the child table, and the foreign key value of a child row must be a value that exists in the parent table. One member can place many orders, so one parent row can be pointed to by many child rows, but one child row points to only one parent row. What to do when a parent row is deleted or changed is set by the referential action of the foreign key, and if you write nothing, the default is NO ACTION.

Try these

Running with the default settings

  1. CREATE TABLE: The database creates two tables, member and orders. Right now they have no rows. The member_id column of orders holds "the number of the member who placed this order." orders has the line FOREIGN KEY (member_id) REFERENCES member(id), so the database remembers a promise: "the value in member_id must be an id that really exists in the member table." This is a foreign key.
  2. INSERT INTO member VALUES (1, 'Alice'): The database adds the row with id 1 and the name Alice to the member table. There is now one member.
  3. INSERT INTO orders VALUES (10, 1): This adds order 10. The 1 in member_id is "the number of the member who placed this order," and it points to the row with id 1 in the member table (Alice). Before it adds the row, the database checks the promise of the foreign key. It looks for the row with id 1 in the member table, finds it, and so the check passes and order 10 goes in. We did not write the code that does this check. The database does it by itself every time an INSERT arrives.
  4. INSERT INTO orders VALUES (11, 99): This tries to insert the second order. Before it adds the row, the database looks for the row with id 99 in the member table. The database does this check by itself, too.
  5. Result of the check: The member table has no row with id 99. That breaks the promise, so the database rejects this INSERT and returns an error of the kind "the referenced row does not exist." Order 11 was not added to orders, and the table is exactly as it was before the INSERT.
  6. DELETE FROM member WHERE id = 1: This tries to delete member 1 (Alice). Before it deletes the row, the database looks in the orders table for orders whose member_id is 1. It finds order 10.
  7. Result of the check: Orders that point to this member are still there, so the database rejects this DELETE and returns an error of the kind "another table references this row." Member 1 was not deleted, and the member table is exactly as it was before the DELETE. So a member that an order points to never disappears.
  8. SELECT * FROM orders ORDER BY id: This reads the orders left in orders, in order of id. The result is (10, 1). Order 11 was rejected and not added, and the remaining order 10 points to member 1, who is in member.
  9. As a side note, we run a separate statement for checking that is not in this code. SELECT COUNT(*) FROM orders o LEFT JOIN member m ON o.member_id = m.id WHERE o.member_id IS NOT NULL AND m.id IS NULL looks in member for a match for every order in orders, and counts the orders that have no match. The result is 0. Because of the foreign key, not a single order without a match could appear.

Result of this run

Code

CREATE TABLE member (
    id   INT PRIMARY KEY,
    name VARCHAR(20)
);
CREATE TABLE orders (
    id        INT PRIMARY KEY,
    member_id INT,
    -- Foreign key: the value in member_id must be an id that exists in the member table
    FOREIGN KEY (member_id) REFERENCES member(id)
);

INSERT INTO member VALUES (1, 'Alice');
INSERT INTO orders VALUES (10, 1);
INSERT INTO orders VALUES (11, 99);

DELETE FROM member WHERE id = 1;
SELECT * FROM orders ORDER BY id;

Assumptions of this model

The model was checked against real execution on H2 2.4.240 (an in-memory database, through JDBC). For all 6 combinations, the whole SQL on the screen (including the comment line) was run character by character, one statement at a time, and when one statement was rejected, the next statement was still run. The following were checked against the model: whether the INSERT of the second order and the DELETE of member 1 were accepted or rejected and the kind of error (H2 SQLSTATE 23506 means the INSERT breaks the foreign key, and 23503 means the DELETE tries to delete a referenced row), and the result of the last SELECT. "Orders pointing to a missing member" is the value that the Probe counted at the end by separately running the checking statement SELECT COUNT(*) FROM orders o LEFT JOIN member m ON o.member_id = m.id WHERE o.member_id IS NOT NULL AND m.id IS NULL, which is not in the code on the screen. The code on the screen does not put only REFERENCES next to the column definition. It writes a separate FOREIGN KEY (member_id) REFERENCES member(id) line. This is because the MySQL 8.4 documentation (section 1.7.2.3 FOREIGN KEY Constraint Differences and the Indexes, Foreign Keys, and CHECK Constraints part of section 15.1.20 CREATE TABLE) says that MySQL only parses and then ignores a REFERENCES written next to a column definition, and accepts only one written in a separate FOREIGN KEY line. I separately checked that H2 accepts both forms as a foreign key. I opened section 15.1.20.5 FOREIGN KEY Constraints of the MySQL 8.4 documentation directly and checked the following: a foreign key constraint is defined on the child table; an INSERT or UPDATE that creates a value in the child table with no matching value in the parent table is rejected; and when ON DELETE is not written, the default action is NO ACTION, which in InnoDB is the same as RESTRICT and immediately rejects the deletion of the parent row. That an INSERT is possible when the foreign key column is NULL, even if the parent has no match, follows section 15.1.20 and the MATCH description in section 1.7.2.3 (MATCH SIMPLE allows a NULL foreign key). The error numbers and the wording of the messages differ from database to database, so the screen shows only the kind of error. The check by execution on MySQL was not done. So the rule above rests on the documentation and the H2 execution agreeing, and it was not verified by execution whether MySQL gives the same result for the same SQL. That a table without a FOREIGN KEY line accepts a member number that does not exist and does not stop the deletion of a member is behavior observed on H2. This lab covers only the default behavior of a foreign key (rejection), and it does not cover referential actions such as ON DELETE CASCADE, UPDATE, NOT NULL, or composite foreign keys.

Previous lab: Primary keys · Next lab: WHERE

Concepts in this lab

What this lab shows

Settings

Code


      
    


    

      
Diagram

      

      

    


    

      

      

      

    


  


  

    

      
Run comparison

      

      

    

    

      
Try these