SQL Constraints — Enforcing Rules at the Table Level

The office must reject repeated roll numbers, missing names and marks outside the school’s allowed range.

A constraint expresses a rule that the database enforces when data is written. Keys, nullability, relationships and permitted values each address a different kind of invalid data.

PRIMARY KEY identifies a row, UNIQUE prohibits repeated permitted key values according to null rules, NOT NULL requires a value and CHECK evaluates a condition. FOREIGN KEY connects child keys to a valid parent key. These rules protect all writers that use the database, rather than only one application screen.

Related concepts: Primary and Unique Keys and Foreign Keys and Missing Values and NULL.

SQL constraints are rules applied to table columns to ensure data integrity. They prevent invalid, duplicate, or inconsistent data from being entered into the database.

• PRIMARY KEY
• UNIQUE
• NOT NULL
• CHECK
• DEFAULT
• FOREIGN KEY

SQL · portable concepts; preserved examples identify their engine

CREATE TABLE students (
  roll_no INT PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  class VARCHAR(10) DEFAULT 'Not Assigned',
  age INT CHECK (age >= 5 AND age <= 18),
  city VARCHAR(30)
);

SQL · portable concepts; preserved examples identify their engine

Ensures that each row has a unique identifier. Cannot be NULL.

-- roll_no is PRIMARY KEY
INSERT INTO students VALUES (1, 'Aarav Sharma', '10A', 15, 'Delhi');

-- This will fail (duplicate roll_no)
INSERT INTO students VALUES (1, 'Diya Iyer', '9B', 14, 'Chennai');

SQL · portable concepts; preserved examples identify their engine

Prevents NULL values in a column.

-- This will fail (name is NOT NULL)
INSERT INTO students (roll_no, age) VALUES (2, 14);

SQL · portable concepts; preserved examples identify their engine

Sets a default value if none is provided during insertion.

INSERT INTO students (roll_no, name, age, city)
VALUES (3, 'Sneha Patil', 13, 'Pune');

roll_no | name           | class         | age | city
--------+----------------+---------------+-----+-------
3       | Sneha Patil    | Not Assigned  | 13  | Pune

SQL · portable concepts; preserved examples identify their engine

Limits the values in a column to meet a specific condition.

-- This will fail (age < 5)
INSERT INTO students VALUES (4, 'Mehul Agarwal', '8C', 4, 'Delhi');

SQL · portable concepts; preserved examples identify their engine

Ensures all values in a column are different.

ALTER TABLE students
ADD CONSTRAINT unique_city UNIQUE(city);

Now, inserting a student with a duplicate city will fail:

-- This will fail if 'Delhi' is already used
INSERT INTO students VALUES (5, 'Saira Bano', '10A', 15, 'Delhi');

SQL · portable concepts; preserved examples identify their engine

Enforces a relationship between two tables.

Table: results

CREATE TABLE results (
  id INT PRIMARY KEY,
  roll_no INT,
  subject VARCHAR(30),
  marks INT,
  FOREIGN KEY (roll_no) REFERENCES students(roll_no)
);

This ensures that results.roll_no must match an existing student.

-- Works if roll_no 1 exists in students
INSERT INTO results VALUES (1, 1, 'Maths', 85);

-- Fails if roll_no 99 doesn’t exist
INSERT INTO results VALUES (2, 99, 'Science', 90);

• Always define PRIMARY KEY for every table.
• Use NOT NULL for mandatory fields like names.
• Use CHECK to enforce logical limits (e.g., age, marks).
• Use FOREIGN KEY to maintain relationships between tables.

In a school database, constraints help prevent invalid data like:

• Duplicate roll numbers (PRIMARY KEY)
• Missing student names (NOT NULL)
• Marks outside valid ranges (CHECK)
• Results for students not in the system (FOREIGN KEY)

A CHECK condition that evaluates to unknown may be accepted, so add NOT NULL when a mark is mandatory. MySQL enforced CHECK constraints beginning with version 8.0.16; older examples need that version boundary. UNIQUE and NULL behavior vary by engine.

Store one valid mark under an explicit 0–100 rule, then explain which extra rule makes a missing mark invalid.

This practice uses a separate, fresh fixture named practice_students. It does not continue the changing table state of the earlier examples.

SQL · create only these practice tables in a disposable database:

CREATE TABLE practice_students (roll_no INTEGER PRIMARY KEY, name VARCHAR(50), class VARCHAR(10), marks INTEGER, city VARCHAR(30));
INSERT INTO practice_students VALUES
(1, 'Aarav', '10A', 85, 'Pune'),
(2, 'Diya', '9B', 70, 'Chennai'),
(3, 'Sneha', '10A', 85, 'Pune');

This practice uses a separate, fresh fixture named practice_students. It does not continue the changing table state of the earlier examples.

SQL · create only these practice tables in a disposable database:

CREATE TABLE practice_students (roll_no INTEGER PRIMARY KEY, name VARCHAR(50), class VARCHAR(10), marks INTEGER, city VARCHAR(30));
INSERT INTO practice_students VALUES
(1, 'Aarav', '10A', 85, 'Pune'),
(2, 'Diya', '9B', 70, 'Chennai'),
(3, 'Sneha', '10A', 85, 'Pune');

SQL · this practice variation runs with SQLite 3; the preserved examples retain their stated dialect

CREATE TABLE practice_checked (roll_no INTEGER PRIMARY KEY, marks INTEGER NOT NULL CHECK(marks BETWEEN 0 AND 100)); INSERT INTO practice_checked VALUES (1,85);
SELECT roll_no, marks FROM practice_checked;

CHECK restricts present mark values; NOT NULL rules out a missing mark. The constraints have complementary responsibilities.

A constraint expresses a rule that the database enforces when data is written. Keys, nullability, relationships and permitted values each address a different kind of invalid data.

CHECK restricts present mark values; NOT NULL rules out a missing mark. The constraints have complementary responsibilities.

Continue your understanding with Primary and Unique Keys and Foreign Keys and Missing Values and NULL.

Source for the engine-specific behavior: MySQL 8.4 reference documentation.