SQL keys and constraints: reject invalid practice submissions

Define validity before accepting records

A project submission needs an identity, an existing learner and a mark in a valid range. Database constraints state these requirements where every database writer must encounter them. An application form can provide friendlier validation messages, but another script or import might bypass that form. The database should still protect its own defined rules.

The example creates a new practice_submissions table after the shared setup. id is an INTEGER PRIMARY KEY. student_id is required and references students(id). score is required and must be between 0 and 100 inclusive. These constraints describe independent aspects of validity; passing one does not imply the others pass.

Understand what each constraint guarantees

A primary key identifies a row. A foreign key connects a child record to an allowed parent key, with enforcement enabled for this SQLite connection. NOT NULL prevents a required value from being missing. CHECK expresses an allowed condition such as a score range. A separate UNIQUE constraint is useful for a business key when repeated values are not allowed.

Pair the score range check with NOT NULL. In SQLite a CHECK result that is NULL does not by itself reject the row, so CHECK(score BETWEEN 0 AND 100) alone does not make score mandatory. This is a good example of why you should test missing input separately from out-of-range input.

Test rejection as well as successful inserts

The runnable example inserts one valid submission and reads it back. In a fresh copy of the database, try a score of 101, a missing score and a nonexistent student ID. Each should fail under the stated setup. Keep these deliberate failure experiments separate from a script that you expect to run to completion without an error.

Constraint errors need an application-level response. A user-facing flow should explain the relevant validation issue and should not report success when an insert failed. For operations involving several writes, transaction boundaries determine whether earlier changes are retained or rolled back. Constraints and transactions solve related but different parts of reliable data handling.

Run the worked example

PRAGMA foreign_keys = ON;
CREATE TABLE students (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    city TEXT,
    score INTEGER NOT NULL CHECK (score BETWEEN 0 AND 100),
    cohort TEXT NOT NULL
);
CREATE TABLE courses (id INTEGER PRIMARY KEY, title TEXT NOT NULL UNIQUE);
CREATE TABLE enrollments (
    student_id INTEGER NOT NULL REFERENCES students(id),
    course_id INTEGER NOT NULL REFERENCES courses(id),
    PRIMARY KEY (student_id, course_id)
);
INSERT INTO students VALUES
    (1, 'Asha', 'Jaipur', 82, 'Java'),
    (2, 'Aman', 'Ajmer', 66, 'Java'),
    (3, 'Jaya', 'Jaipur', 94, 'SQL'),
    (4, 'Kiran', NULL, 48, 'SQL'),
    (5, 'Neha', 'Kota', 82, 'SQL'),
    (6, 'Kabir', 'Jaipur', 40, 'Java');
INSERT INTO courses VALUES (10, 'Java'), (20, 'SQL'), (30, 'Python');
INSERT INTO enrollments VALUES (1, 10), (1, 20), (2, 10), (3, 20), (5, 20);

Download setup.sql

CREATE TABLE practice_submissions (
    id INTEGER PRIMARY KEY,
    student_id INTEGER NOT NULL REFERENCES students(id),
    score INTEGER NOT NULL CHECK (score BETWEEN 0 AND 100)
);
INSERT INTO practice_submissions VALUES (1, 1, 80);
SELECT id, student_id, score FROM practice_submissions ORDER BY id;

Expected result

1. The existing student ID 1 satisfies the foreign key.
2. 80 satisfies the inclusive score range and is not missing.
3. The final SELECT proves that the valid record was stored.

Try it yourself

Create attempt_records with an integer primary key, required student_id referencing students, and attempts required to be at least 1. Insert (1, 2, 3) and select the row.

CREATE TABLE attempt_records (
    id INTEGER PRIMARY KEY,
    student_id INTEGER NOT NULL REFERENCES students(id),
    attempts INTEGER NOT NULL CHECK (attempts >= 1)
);
INSERT INTO attempt_records VALUES (1, 2, 3);
SELECT id, student_id, attempts FROM attempt_records ORDER BY id;

Quick check

Choose an answer, then check the explanation. Your answer stays in this page and is not saved as a student record.

Keep learning

Explain the result in your own words, change one input and predict the new output. For additional structured assignments, explore the workbook sample before requesting the full pack.