The key to the flat: primary and foreign keys
About this app
Every row of tools is a flat, id its key: unique and never empty, otherwise UNIQUE or NOT NULL constraint failed. products.tool points into the other table as a foreign key, key 2 opens the door to beta for round. When the workshop deletes tool 2, two references point into nothing like a dead link; with a declared foreign key the database refuses: FOREIGN KEY constraint failed.
With explain mode: an AI tutor walks you through the app, points out what matters in the scene and answers your questions.
Primary key: unique and never empty
A primary key is the column through which every row of a table can be reached exactly once, such as id in tools. It has to meet two conditions: it is unique, two rows with id 1 are not allowed, and it is never empty, a row without an id is not allowed. SQLite reports the violations as UNIQUE constraint failed: tools.id and NOT NULL constraint failed: tools.id. A foreign key stores the primary key of a row in another table, so products.tool points to tools.id.
What the app shows
Every row of tools is an apartment with a door and a number, the value in id its key. Keys 1, 2 and 3 open their doors; a second row with id 1 and a row with NULL are rejected with the real SQLite message. Then the references round → 2, square → 1 and triangle → 2 point from products to tools, and key 2 opens door 2: round | beta. Finally tool 2 is deleted, two references point into nothing, and with a declared foreign key the database refuses.
Try it yourself
Pick one of the three scenes apartment, foreign key and delete and go through the steps. Try the duplicate id and the empty id, follow the key from round to its tool and delete tool 2 once without and once with a foreign key. The numbers in the panel show rows, the open door, references and references into nothing.
Frequently asked questions
- What is the difference between a primary key and a foreign key?
- The primary key identifies every row of its own table uniquely and is never NULL. A foreign key is a column in another table that stores values of this primary key and so refers to a row. Several rows may have the same foreign key value.
- What does FOREIGN KEY constraint failed mean?
- The database refused a change because afterwards a reference would point into nothing, for example when deleting a row that a foreign key still points to. It checks this only if the foreign key is declared; in SQLite PRAGMA foreign_keys = ON must be set as well.
- Can a primary key be NULL?
- By the SQL standard no, a primary key is always NOT NULL. For historical reasons SQLite allows NULL in some primary keys if not null is missing; with integer primary key SQLite assigns the next number for NULL itself.
- What can I try in the app?
- Three scenes with steps: keys open doors in tools, a duplicate and an empty id are rejected, a key travels from products to tools, and tool 2 is deleted with and without a foreign key. An explanation mode guides you through everything.
Subject: Computer science
More from Computer science
- Ackermann function
- Elevator simulator
- Binary workshop
- Binary versus linear search
- The Collatz problem
- Inside a CPU
- Swap the inside
- One frame in 16 milliseconds — how a game engine renders an image (3D)
A VisuApp by heyprof: interactive, free in your browser, no sign-up. What is a VisuApp? · All apps