01.3 Creating tables and constraints

01 | FROM A PLAN TO SQL

We have planned tables and relationships. Now we will ask SQLite to create a structure and enforce selected rules. SQL stands for Structured Query Language.

These examples use SQLite. Its SQL overlaps with other products, but types, generated identifiers and some constraint details differ. Use the matching database engine when trying the examples.

The following Python wrapper is just a convenient way to run SQL in an editor you may already use. You do not need to understand all the Python to study the SQL inside the triple quotes.

02 | OPEN THE PRACTICE FILE IN VS CODE

Open your learning folder in VS Code, create a file called sql_practice.py and select your Python interpreter. With the Python extension installed, use Run Python File , the triangle button in the editor.

Most standard Python installations include the sqlite3 module; there is normally no pip package to install. If your installation lacks it, use a Python distribution with SQLite support or ask your teacher for a configured SQLite editor.

Copy the complete starter below. The database lives in memory, so each click of Run starts fresh. It does not create a persistent database file. Results and database errors appear in VS Code’s terminal panel.

import sqlite3

# A fresh temporary database is created every time you click Run.
connection = sqlite3.connect(":memory:")
connection.execute("PRAGMA foreign_keys = ON")

sql = """
CREATE TABLE Students (
    student_id INTEGER PRIMARY KEY,
    first_name TEXT NOT NULL,
    year_level INTEGER NOT NULL CHECK (year_level BETWEEN 7 AND 13)
);

INSERT INTO Students (student_id, first_name, year_level)
VALUES (101, 'Maya', 10), (102, 'Leo', 11);
"""

try:
    connection.executescript(sql)
    rows = connection.execute(
        "SELECT student_id, first_name, year_level FROM Students ORDER BY student_id"
    )
    for row in rows:
        print(row)
except sqlite3.Error as error:
    print("Database error:", error)
finally:
    connection.close()

03 | READ CREATE TABLE LINE BY LINE

CREATE TABLE Students (
    student_id INTEGER PRIMARY KEY,
    first_name TEXT NOT NULL,
    year_level INTEGER NOT NULL CHECK (year_level BETWEEN 7 AND 13)
);

CREATE TABLE Students names the new table. Parentheses contain column definitions separated by commas. Each starts with a column name and declared type, followed by any rules.

There is no comma after the last definition. The semicolon ends the statement. The starter runs this SQL and inserts two rows, then prints (101, 'Maya', 10) and (102, 'Leo', 11) .

In a persistent database, running CREATE TABLE a second time can produce “table already exists”. Our in-memory runner avoids that. IF NOT EXISTS can suppress that error, but does not update an existing table’s structure.

04 | PRIMARY KEY AND GENERATED IDENTIFIERS

In SQLite, the exact declaration INTEGER PRIMARY KEY has special row-identifier behaviour. If an INSERT omits that column, SQLite can assign an integer identifier.

INSERT INTO Students (first_name, year_level)
VALUES ('Sam', 9);

Add this inside the SQL string after the existing INSERT, then Run again. The printed result includes another student with an assigned ID. Do not depend on identifiers being gap-free or a permanent sequence of row positions.

AUTOINCREMENT is not required for automatic assignment here. It has a more specific SQLite purpose and adds overhead; this project does not need it.

05 | NOT NULL IS NOT THE SAME AS NOT BLANK

NOT NULL rejects missing NULL values. It does not reject an empty text value such as '' .

To require a name containing something beyond ordinary spaces, combine it with a check:

first_name TEXT NOT NULL
    CHECK (length(trim(first_name)) > 0)

trim removes ordinary outer spaces here, and length counts remaining characters. This is a useful beginner rule, not a complete test for every kind of whitespace or a real person’s identity.

06 | CHECK THE VALUES YOU ACTUALLY NEED

A CHECK accepts values satisfying an expression. The simple year range rule in the starter rejects year 6 or 14, but a range alone does not express “whole number” in an ordinary flexible-typed SQLite table.

year_level INTEGER NOT NULL
    CHECK (typeof(year_level) = 'integer'
           AND year_level BETWEEN 7 AND 13)

typeof checks the storage class after SQLite applies affinity. A value stored as a whole-number integer from 7 to 13 passes; 10.5 fails. A numeric string such as '10' can be converted to an integer and pass.

A CHECK evaluating to NULL does not fail in SQLite, so use NOT NULL as well for required values.

07 | UNIQUE AND DEFAULT

CREATE TABLE Clubs (
    club_id INTEGER PRIMARY KEY,
    club_name TEXT NOT NULL UNIQUE,
    capacity INTEGER NOT NULL CHECK (capacity >= 1)
);

UNIQUE prevents duplicate non-NULL values under the column’s comparison rules. Here the name is required too. Ordinary SQLite text comparison is case-sensitive by default, so Robotics and robotics may count as different names.

DEFAULT supplies a value when a column is omitted from INSERT. It does not replace an explicitly supplied NULL. We will use role TEXT NOT NULL DEFAULT 'member' so omitting the role creates an ordinary membership.

08 | CREATE THE REFERENCES

CREATE TABLE Memberships (
    student_id INTEGER NOT NULL,
    club_id INTEGER NOT NULL,
    role TEXT NOT NULL DEFAULT 'member'
        CHECK (role IN ('member', 'leader')),
    PRIMARY KEY (student_id, club_id),
    FOREIGN KEY (student_id) REFERENCES Students(student_id),
    FOREIGN KEY (club_id) REFERENCES Clubs(club_id)
);

The table-level primary key protects the pair. Each FOREIGN KEY specifies a local field and its referenced table and field. A membership needs existing student and club records, so create and populate the parent tables before inserting memberships.

In SQLite, enable enforcement with PRAGMA foreign_keys = ON; on each connection before a transaction starts. Run PRAGMA foreign_keys; to check: 1 means enabled. The supplied Python runner already enables it.

09 | INSERT IS HOW DATA ENTERS

INSERT INTO Clubs (club_id, club_name, capacity)
VALUES (1, 'Robotics', 12);

INSERT INTO Memberships (student_id, club_id)
VALUES (101, 1);

INSERT INTO names the destination and columns. VALUES provides corresponding values in the same order. SQL text literals use single quotes; numbers here do not.

The membership omits role, so DEFAULT supplies member. The student 101 already exists in the starter. Add Clubs and Memberships definitions after Students is created, then place these inserts after the student insert.

The runner’s final SELECT still prints Students only. Page 01.4 shows how to inspect all three tables.

10 | TEST ONE FAILURE AT A TIME

After building the valid example, add one of the following test statements at the end of the SQL string, Run, read the error, then remove it before testing another.

-- Duplicate club ID: should fail PRIMARY KEY
INSERT INTO Clubs VALUES (1, 'Coding', 10);

-- Unknown student: should fail FOREIGN KEY
INSERT INTO Memberships (student_id, club_id) VALUES (999, 1);

-- Disallowed role: should fail CHECK
INSERT INTO Memberships VALUES (102, 1, 'captain');

Use only one invalid statement at a time. The script stops at the first error. Also choose an unused pair when isolating a role check, so you do not accidentally test duplicate membership instead.

11 | BUILD YOUR OWN RULE

Add an equipment_count column to Clubs. It should be required, default to zero and allow only non-negative whole numbers.

KEEP LEARNING

Previous guide 01.4 Designing a small relational database

Reference: SQLite table constraints · SQLite data types · SQLite foreign keys · Python SQLite interface