Dev Learning Lab

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

Learn › Database › Relational model and design

Tables, keys, and constraints

What data gets in when there are no constraints?

Insert wrong values into the member table and the orders table, or delete a member who still has orders. A table that has constraints rejects those statements with an error, and a table without constraints accepts them as they are.

Checked against real execution · The SQL of all 48 combinations was run as it is on MySQL 8.4.11, and the error numbers, the number of rows changed, and all the rows of both tables afterwards were checked

Concepts in this lab

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.
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.
NOT NULL and CHECK
These are constraints that limit the values that can go into a single column. NOT NULL does not allow the column to be left empty (NULL), and CHECK does not accept a value that makes the condition written in the parentheses false. For example, CHECK (age >= 0) blocks a negative age. If the result of the condition is NULL (unknown), CHECK lets the value pass, so if a value must exist, add NOT NULL as well. MySQL actually enforces CHECK from 8.0.16, and versions before that ignored a CHECK even if you wrote one.
Foreign key referential actions (ON DELETE)
These are the actions that you set on a foreign key to decide what happens to the child rows that point to a parent row when that parent row is deleted. RESTRICT rejects the deletion of the parent if child rows remain. CASCADE deletes the child rows along with the parent, and SET NULL keeps the child rows and changes only the foreign key column to NULL. If you write nothing, the default is NO ACTION, which behaves the same as RESTRICT in MySQL's InnoDB. One statement with CASCADE can also delete rows of another table, so use it only for data that may disappear together, and SET NULL can be used only when the foreign key column can accept NULL. What happens when the key value of the parent is changed is set in the same way with ON UPDATE.

Try these

Running with the default settings

  1. CREATE TABLE, INSERT: Two tables are created, and 3 members and 3 orders are inserted. Only the names and types of the columns are defined, and no constraint is applied at all. That member_id in orders is a member number is known only to people, and the DB does not know it.
  2. DELETE FROM member WHERE id = 1: Tries to delete member 1 (Kim). In orders, there are 2 orders (101 and 102) that point to member 1.
  3. There is no constraint to check: The DB only looks at whether the values fit the types, and runs the statement as it is. 1 row was deleted from member. orders was not touched.
  4. SELECT COUNT(*) FROM orders o LEFT JOIN member m ON m.id = o.member_id WHERE o.member_id IS NOT NULL AND m.id IS NULL (check query). Orders without a member: 2. Orders 101 and 102 still have member_id 1, but member 1 no longer exists. 2 orders without an owner remain.

Result of this run

Code

-- Tables without constraints
CREATE TABLE member (
  id    INT,
  email VARCHAR(50),
  name  VARCHAR(20),
  age   INT
);
CREATE TABLE orders (
  id        INT,
  member_id INT,
  item      VARCHAR(30)
);

-- Initial data
INSERT INTO member (id, email, name, age) VALUES
  (1, 'kim@lab.example', 'Kim', 30),
  (2, 'lee@lab.example', 'Lee', 25),
  (3, NULL, 'Park', 41);
INSERT INTO orders (id, member_id, item) VALUES
  (101, 1, 'desk'),
  (102, 1, 'mouse'),
  (103, 2, 'monitor');

-- Statement to run
DELETE FROM member WHERE id = 1;

-- Check query
SELECT COUNT(*)
  FROM orders o
  LEFT JOIN member m ON m.id = o.member_id
 WHERE o.member_id IS NOT NULL AND m.id IS NULL;

Assumptions of this model

This is a small example with 3 members and 3 orders. For all 48 combinations (8 statements × with or without constraints × 3 ON DELETE actions), the SQL on the screen was run as it is on a real MySQL 8.4.11 (InnoDB, default sql_mode), and the error number and error message, the number of rows changed, all the rows of the two tables afterwards, and the result of the check query were checked to be the same as in the model. The error numbers and messages are those of MySQL, and they differ in other databases. That an UPDATE that changes a NOT NULL column to NULL is rejected with error 1048 holds in strict SQL mode (the default in MySQL 8), and when the same UPDATE is run in a session with strict mode turned off, it saves an empty string without an error. That NULL can go into a unique column several times is behavior that was confirmed by running it on MySQL, and some databases, such as SQL Server, allow NULL only once (this comparison followed the documentation and was not checked by execution). When you do not write ON DELETE, MySQL behaves the same as RESTRICT (NO ACTION), and in InnoDB there is no difference between the two. That the child rows deleted together by CASCADE are not included in the number of rows changed is also MySQL behavior that was confirmed by running it. It was also confirmed by running it that when you write only REFERENCES right after a column definition (member_id INT REFERENCES member(id)), MySQL accepts it without an error but does not create a foreign key. That is why the screen writes a separate FOREIGN KEY clause. The foreign keys are for InnoDB, and this lab covers only the case where one statement is run at a time.

Next lab: ER modeling

Concepts in this lab

What this lab shows

Settings

Code


      
    


    

      
Diagram

      

      

    


    

      

      

      

    


  


  

    

      
Run comparison

      

      

    

    

      
Try these