Dev Learning Lab

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

Fundamentals › Database

Primary keys

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

A primary key is what you get when you write PRIMARY KEY on one column while creating a table. It is a promise to the database that the value in that column must be different in every row and cannot be empty (NULL), so the database rejects any INSERT that breaks the promise. That is why "the row with id 1" always points to exactly one row. In this lab you remove PRIMARY KEY, or change the id of the second row you insert, and run the same SQL each time. You see whether the database accepts the INSERT and how many rows the UPDATE changes.

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.
NULL and three-valued logic
NULL stands for "no value" or "unknown", not for 0 or an empty string. A comparison with NULL (=, <>, <, and so on) is neither true nor false but "unknown" (UNKNOWN), so a condition in SQL has three values: true, false, and unknown. WHERE and HAVING keep only the rows for which the condition is true, so a row for which it is unknown is removed, just like a row for which it is false. NOT applied to unknown is still unknown. To check for NULL, use IS NULL and IS NOT NULL, not = NULL.
Rows and columns
A table in a relational database is made of columns and rows, like a spreadsheet. A column is a kind of cell whose name and type are fixed when the table is created. For example, id and name are the columns of the member table. A row is one line filled with values for those columns, and the information about one member is one row. INSERT adds one row, UPDATE changes every row that matches the WHERE condition, and SELECT reads rows. A column with no value is shown as NULL.

Try these

Running with the default settings

  1. CREATE TABLE: The database creates a table named member. A table is made of columns (id and name) and rows (one line of information about one person), and right now it has no rows. PRIMARY KEY is written on the id column, so the database remembers a promise: "the value in this column must be different in every row and cannot be empty."
  2. INSERT INTO member VALUES (1, 'Alice'): Before it adds the row, the database checks the promise. The table is empty, so no row has the same id, and the id is not empty. The row passes, and the Alice row is inserted.
  3. INSERT INTO member VALUES (1, 'Bob'): This tries to insert the second row. Before it adds the row, the database checks the promise of the primary key. It looks at whether a row with id 1 already exists in the table and whether the id is non-empty. We did not write the code that does this check. The database does it by itself every time an INSERT arrives.
  4. Result of the check: The table already has a row with id 1 (Alice). That breaks the promise, so the database rejects this INSERT and returns an error of the kind "this value already exists." The Bob row was not added to the table, and the table is exactly as it was before the INSERT.
  5. UPDATE member SET name = 'Carol' WHERE id = 1: What we want to do is change the name of the one person whose id is 1. The database finds every row that matches the WHERE condition and changes it. There is only one row with id 1, so the name of that row changes to Carol. Rows changed: 1.
  6. SELECT * FROM member ORDER BY id: This reads the rows left in the table in order of id. The result is (1, Carol). What is left is one row with id 1. The Bob row was rejected and not added, and the name in the Alice row was changed to Carol. "The row with id 1" is always this one row.

Result of this run

Code

CREATE TABLE member (
    -- Primary key: the value in this column must differ in every row and cannot be empty
    id   INT PRIMARY KEY,
    name VARCHAR(20)
);

INSERT INTO member VALUES (1, 'Alice');
INSERT INTO member VALUES (1, 'Bob');

UPDATE member SET name = 'Carol' WHERE id = 1;
SELECT * FROM member 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 the following were checked against the model: whether the second INSERT went in or was rejected and the kind of error (H2 SQLSTATE 23505 for a duplicate and 23502 for NULL not allowed), the number of rows after the two INSERTs, the number of changed rows that the UPDATE returned, and the result of the last SELECT. The values in the last step that looks for the row with the empty id (id = NULL gives 0 rows, and id IS NULL gives every row with an empty id) were checked by running a statement that is not in the code on the screen, separately on H2. The rule that a primary key accepts neither a repeated value nor NULL is the same in MySQL, and I opened the MySQL 8.4 documentation directly to check the CREATE TABLE statement (a PRIMARY KEY is a unique index whose key columns are all NOT NULL), section 1.7.3.1 PRIMARY KEY and UNIQUE Index Constraints (an INSERT or UPDATE that violates a primary key or unique key is an error), and section 5.3.4.6 Working with NULL Values (NULL cannot be compared with =, you use IS NULL, and in ascending ORDER BY, NULL comes first). 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 has not been done yet. 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 PRIMARY KEY accepts the same id or NULL is behavior observed on H2. This lab covers PRIMARY KEY only, and it does not cover unique keys, foreign keys, or automatic increment (AUTO_INCREMENT).

Previous lab: Tables, rows, and columns · Next lab: Foreign keys

Concepts in this lab

What this lab shows

Settings

Code


      
    


    

      
Diagram

      

      

    


    

      

      

      

    


  


  

    

      
Run comparison

      

      

    

    

      
Try these