SQL tables, keys, and constraints

What you will learn

• Create related tables with primary, unique and foreign keys.
• Observe a database rejecting invalid task data.
• Explain why constraints and application validation work together.

Theory: keep related data consistent

A table contains rows with named columns. A primary key identifies a row; another row cannot reuse it. A unique constraint prevents duplicate non-null values in the constrained key. This lab also makes email NOT NULL. The exact equality of text depends on the database collation; do not assume every product treats letter case identically.

A foreign key links a child value to an existing parent key. Here, a task belongs to one person and the integer types match. InnoDB checks the relationship. ON DELETE RESTRICT prevents deleting a person while their tasks still refer to them. This is a deliberate data policy; it is not automatic user authorization.

NOT NULL requires a value. A CHECK restricts an expression, such as allowed statuses. CHECK does not replace NOT NULL: a check can evaluate to unknown for null. Application validation gives useful messages before a write; database constraints protect consistency when different writers use the database. Neither grants permission to read or change someone else's work.

Prepare an isolated practice database

Use MySQL 8.4, a SQL client, and a database account allowed to create practice tables. MariaDB and older MySQL versions can behave differently. Windows and Ubuntu run the same SQL. Laragon/XAMPP are optional ways to obtain a server/client; verify the actual server version.

Create a new empty database using your database tool. For example, an account with permission can run CREATE DATABASE devpath_sql_practice; then USE devpath_sql_practice;. If the name exists, choose a new name; do not delete its contents. Do not use this site's application database or company/customer data. Run SELECT DATABASE(), VERSION(); and SHOW TABLES; to confirm the selected database is the empty practice one. In a GUI, keep the same connection and selected schema for every statement. In a terminal, use the installed mysql client and an interactive password prompt rather than writing a password in command history. Ask your database administrator for a practice database if creation is unavailable.

Worked example: people and tasks

Run these statements once in that empty practice database. The script creates only new practice tables and dummy records; a repeat CREATE is expected to fail because the tables already exist.

CREATE TABLE study_people (
    id INT UNSIGNED PRIMARY KEY,
    email VARCHAR(120) NOT NULL UNIQUE,
    name VARCHAR(80) NOT NULL
) ENGINE=InnoDB;

CREATE TABLE study_tasks (
    id INT UNSIGNED PRIMARY KEY,
    person_id INT UNSIGNED NOT NULL,
    title VARCHAR(80) NOT NULL CHECK (CHAR_LENGTH(TRIM(title)) > 0),
    status VARCHAR(20) NOT NULL CHECK (status IN ('todo', 'in_progress', 'done')),
    FOREIGN KEY (person_id) REFERENCES study_people(id) ON DELETE RESTRICT
) ENGINE=InnoDB;

INSERT INTO study_people (id, email, name) VALUES
    (1, 'asha@example.invalid', 'Asha'),
    (2, 'robin@example.invalid', 'Robin'),
    (3, 'maya@example.invalid', 'Maya');

INSERT INTO study_tasks (id, person_id, title, status) VALUES
    (10, 1, 'Read HTTP', 'done'),
    (11, 1, 'Write tests', 'todo'),
    (12, 2, 'Review code', 'todo');

Inspect with SELECT * FROM study_people ORDER BY id; and SELECT * FROM study_tasks ORDER BY id;. Expect three people and three tasks. Asha has task IDs 10 and 11, Robin has 12, and Maya has none. SQL strings use single quotes. The column list makes each inserted value's destination clear.

Guided lab: rejected writes

Run each statement separately. Every one should fail, and task count should stay three:

INSERT INTO study_tasks VALUES (10, 1, 'Duplicate', 'todo');
INSERT INTO study_tasks VALUES (13, 999, 'Missing person', 'todo');
INSERT INTO study_tasks VALUES (14, 1, 'Wrong status', 'blocked');
INSERT INTO study_tasks VALUES (15, 1, '', 'todo');
INSERT INTO study_tasks VALUES (16, 1, NULL, 'todo');

Expect primary-key, foreign-key, CHECK, CHECK, and NOT NULL failures respectively. Client wording varies. Record the failed rule and verify stored rows afterward; an error message alone is not the whole check. Also try DELETE FROM study_people WHERE id = 1;: the foreign key must refuse it while Asha's tasks exist. Do not disable foreign-key checks to force it through. SQL DDL such as CREATE TABLE can commit transactions implicitly in MySQL; wrapping arbitrary setup in a transaction is not a safe undo plan.

Independent exercise

Add person 4 with a unique dummy email, then add task 13 linked to that person with an allowed status. Predict both row counts before executing. Try a second person with the same email; confirm rejection. Explain which rule prevents an orphan task and why validation in PHP is still useful. Keep this practice database for the joins lesson.

Common mistakes

• Connecting to the wrong database: check DATABASE before creating or changing data.
• Mismatching signed and unsigned relationship columns.
• Assuming a foreign key checks ownership permission.
• Using only a UI dropdown to enforce allowed values.
• Re-running seed statements and ignoring duplicate failures.

Self-check — not graded

1. Can two people share the same primary key?
2. Can a task refer to person 999 when that person does not exist?
3. Does the status CHECK authorize edits?

Interview practice

“Why validate data if the database has constraints?” Describe friendly field errors, consistent rules across writers, race conditions between a check and insert, and handling a rejected write. Authorization remains a separate application responsibility.

English practice and glossary

• Row: one stored record.
• Primary key: a row's unique identity.
• Foreign key: a checked reference to another key.
• Constraint: a database rule.
• Orphan: a child referring to a missing parent.

Write: “The insert failed because __. The database still contains __ rows. I confirmed this using __.” Use dummy values.

Recap and next step

Select the correct database, define relationships and constraints, and verify rejected writes. Next, join people and tasks while keeping people who have no tasks. Official MySQL 8.4 references below were checked on 2026-10-22.