SQL I: defining and modifying data

SQL is the language every relational DBMS speaks. This lesson covers the data definition part, which creates tables with columns, types, keys and constraints, and the data modification part, which inserts, updates and deletes rows. It also introduces the college database used in the examples for the rest of the course: departments, instructors, students, courses, prerequisites, course sections and enrollments. Every SQL example in these lessons was run in SQLite 3.45, and its real output is shown. Where SQLite differs from the SQL standard or from servers such as PostgreSQL, MySQL and MariaDB, the text points out the difference.

SQL basics

An SQL program is a sequence of statements, each ending with a semicolon. Keywords such as SELECT and CREATE are not case-sensitive; by convention they are written in capitals so that they stand out from table and column names. Identifiers such as table names are also case-insensitive in most systems unless written in double quotes. String literals use single quotes: 'Computing Science'. Two hyphens start a comment that runs to the end of the line. Whitespace and line breaks are free, so long statements are laid out over several lines for readability.

Each column has a data type. The standard defines many, and every system adds its own. The common core is INTEGER and SMALLINT or BIGINT for whole numbers; DECIMAL(p, s) or NUMERIC for exact decimals such as money; REAL or DOUBLE PRECISION for approximate floating-point numbers; CHAR(n) and VARCHAR(n) for strings, or TEXT; DATE, TIME and TIMESTAMP for dates and times; and BOOLEAN. Money should never be stored as floating point, because binary fractions cannot represent amounts like 0.10 exactly, as CMPUT-229 showed; use DECIMAL or store whole cents as integers.

SQLite is unusual in using type affinity: a column's declared type is a preference rather than a rule, so by default a TEXT value can be stored in an INTEGER column. Since version 3.37 a table declared with the STRICT keyword enforces types as other systems do. MySQL and MariaDB have historically been permissive too, silently truncating or converting bad values unless strict SQL mode is on; modern versions turn strict mode on by default. PostgreSQL is strict. Relying on lax behavior is a mistake: declare constraints, and use strict modes where available.

Creating tables

CREATE TABLE names a table and lists its columns with types and constraints. Column constraints attach to one column; table constraints, written after the columns, can involve several. The constraints that matter most are NOT NULL, which forbids missing values; UNIQUE, which forbids duplicates; PRIMARY KEY, which combines UNIQUE and NOT NULL and names the main identifier; FOREIGN KEY ... REFERENCES, which enforces referential integrity; CHECK, which requires a condition to be true or unknown; and DEFAULT, which supplies a value when none is given. Here is the complete schema of the college database.

CREATE TABLE department (
    dept_id   TEXT PRIMARY KEY,
    name      TEXT NOT NULL UNIQUE,
    building  TEXT,
    budget    INTEGER CHECK (budget >= 0)
);
CREATE TABLE instructor (
    instr_id  INTEGER PRIMARY KEY,
    name      TEXT NOT NULL,
    dept_id   TEXT REFERENCES department(dept_id),
    salary    INTEGER CHECK (salary > 0),
    mentor_id INTEGER REFERENCES instructor(instr_id)
);
CREATE TABLE student (
    student_id INTEGER PRIMARY KEY,
    name       TEXT NOT NULL,
    dept_id    TEXT REFERENCES department(dept_id),
    credits    INTEGER NOT NULL DEFAULT 0 CHECK (credits >= 0)
);
CREATE TABLE course (
    course_id TEXT PRIMARY KEY,
    title     TEXT NOT NULL,
    dept_id   TEXT NOT NULL REFERENCES department(dept_id),
    credits   INTEGER NOT NULL CHECK (credits BETWEEN 1 AND 6)
);
CREATE TABLE prereq (
    course_id TEXT REFERENCES course(course_id) ON DELETE CASCADE,
    prereq_id TEXT REFERENCES course(course_id),
    PRIMARY KEY (course_id, prereq_id)
);
CREATE TABLE section (
    sec_id    INTEGER PRIMARY KEY,
    course_id TEXT NOT NULL REFERENCES course(course_id),
    term      TEXT NOT NULL CHECK (term IN ('Fall', 'Winter', 'Summer')),
    year      INTEGER NOT NULL,
    instr_id  INTEGER REFERENCES instructor(instr_id),
    room      TEXT,
    capacity  INTEGER CHECK (capacity > 0)
);
CREATE TABLE enrollment (
    student_id INTEGER REFERENCES student(student_id) ON DELETE CASCADE,
    sec_id     INTEGER REFERENCES section(sec_id) ON DELETE CASCADE,
    grade      INTEGER CHECK (grade BETWEEN 0 AND 100),
    PRIMARY KEY (student_id, sec_id)
);

SELECT name FROM sqlite_schema WHERE type = 'table' ORDER BY name;

name
----------
course
department
enrollment
instructor
prereq
section
student

Several design choices are visible. Departments use short codes as natural primary keys. Instructors, students and sections use integer surrogate keys. The prereq table has a composite primary key of two columns, because a course can have several prerequisites and a prerequisite can serve several courses; the pair is what must be unique. Enrollment, likewise, is keyed by the pair of student and section, and its grade may be NULL while a course is in progress. Instructor has a foreign key to itself, mentor_id, so that one instructor can mentor others, which lesson 5 uses for a recursive query.

One SQLite detail matters for every example: SQLite does not enforce foreign keys unless each connection runs PRAGMA foreign_keys = ON, a setting kept off by default for backward compatibility. The examples in this course turn it on. PostgreSQL, MariaDB with the InnoDB storage engine, and other servers always enforce declared foreign keys. Forgetting the pragma is a classic way to end up with orphaned rows in an SQLite application.

Inserting data

INSERT adds rows. The safest form names the columns, so that the statement keeps working if columns are later added or reordered, and lists one or more rows of values. Columns that are not named get their DEFAULT value or NULL. INSERT ... SELECT copies the result of a query into a table. Each inserted row is checked against every constraint; if any check fails, the statement is rejected and, by default, none of its rows are inserted.

INSERT INTO student (student_id, name, dept_id)
VALUES (111, 'Kiri', 'PHYS');                   -- credits takes its DEFAULT of 0

SELECT * FROM student WHERE student_id = 111;

INSERT INTO student (student_id, name, dept_id, credits)
VALUES (112, 'Leo', 'ENG', 10);                  -- no ENG department exists

INSERT INTO student (student_id, name, credits)
VALUES (113, 'Mina', -5);                        -- violates CHECK (credits >= 0)

INSERT INTO student (student_id, name)
VALUES (101, 'Duplicate');                       -- 101 already exists

(1 row affected)

student_id  name  dept_id  credits
----------  ----  -------  -------
111         Kiri  PHYS     0

Error: FOREIGN KEY constraint failed

Error: CHECK constraint failed: credits >= 0

Error: UNIQUE constraint failed: student.student_id

Each failed insert names the constraint that stopped it. These messages are the DBMS doing exactly its job: an application with a bug, or a user typing into a form, cannot put the database into a state that breaks the declared rules. Without the constraints, the second insert would create a student in a department that does not exist, and every later report joining students to departments would silently lose Leo.

Updating and deleting

UPDATE changes values in existing rows. Its WHERE clause chooses which rows; without one, every row in the table changes, which is one of the most common and costly mistakes in SQL. SET can use expressions involving the current values. DELETE removes the rows chosen by its WHERE clause, and again, without one, it removes every row. A good habit before running an UPDATE or DELETE on important data is to run a SELECT with the same WHERE clause first and check which rows it chooses, and to do the change inside a transaction that can be rolled back.

-- Give every Mathematics instructor a 3% raise, rounded to the dollar
SELECT name, salary FROM instructor WHERE dept_id = 'MATH';

UPDATE instructor
SET salary = CAST(ROUND(salary * 1.03) AS INTEGER)
WHERE dept_id = 'MATH';

SELECT name, salary FROM instructor WHERE dept_id = 'MATH';

-- Record a final grade for an enrollment that was in progress
UPDATE enrollment SET grade = 87 WHERE student_id = 101 AND sec_id = 5;
SELECT * FROM enrollment WHERE student_id = 101 AND sec_id = 5;

name      salary
--------  ------
Nakamura  105000
Ferreira  91000

(2 rows affected)

name      salary
--------  ------
Nakamura  108150
Ferreira  93730

(1 row affected)

student_id  sec_id  grade
----------  ------  -----
101         5       87

What happens to references on delete

Deleting or changing a row that other rows refer to raises a question: what should happen to the referring rows? A foreign key declaration can specify an action. NO ACTION or RESTRICT, the default, rejects the delete while references exist. CASCADE deletes the referring rows too, or updates them to the new key value for ON UPDATE CASCADE. SET NULL sets the foreign key to NULL; SET DEFAULT sets it to its default. Which to choose depends on meaning. Enrollments make no sense without their student, so the college schema cascades deletes from student to enrollment. A department should not be deleted while students belong to it, so that reference uses the default and the delete is refused.

PRAGMA foreign_keys = ON;
SELECT COUNT(*) AS enrollments_for_101 FROM enrollment WHERE student_id = 101;

DELETE FROM student WHERE student_id = 101;      -- ON DELETE CASCADE removes the enrollments

SELECT COUNT(*) AS enrollments_for_101 FROM enrollment WHERE student_id = 101;

DELETE FROM department WHERE dept_id = 'CS';     -- refused: students and instructors refer to CS

enrollments_for_101
-------------------
4

(1 row affected)

enrollments_for_101
-------------------
0

Error: FOREIGN KEY constraint failed

CASCADE is powerful and dangerous. A chain of cascades can delete far more than intended: deleting one course might cascade to its sections, then to all their enrollments. Use it only where the dependent rows truly have no meaning without the parent, and prefer restricting deletes of important reference data. Many applications avoid deleting such rows at all, marking them inactive instead, which also preserves history.

Changing a schema

ALTER TABLE changes an existing table: adding a column, renaming a column or table, dropping a column, or adding a constraint. DROP TABLE removes a table and its data. Systems differ in what ALTER supports; SQLite historically supported only a few forms and still cannot add most constraints to an existing table, so the usual SQLite approach is to create a new table, copy the data, drop the old one and rename. In production, schema changes are applied through versioned migration scripts kept in source control, so every copy of the database, from a developer's laptop to production, goes through the same steps in the same order.

ALTER TABLE student ADD COLUMN email TEXT;
UPDATE student SET email = lower(name) || '@college.example' WHERE dept_id = 'CS';
SELECT student_id, name, email FROM student WHERE dept_id IN ('CS', 'MATH') ORDER BY student_id;

CREATE UNIQUE INDEX student_email_uk ON student(email);   -- enforce uniqueness of non-NULL emails
INSERT INTO student (student_id, name, email) VALUES (120, 'Ava Two', 'ava@college.example');

(4 rows affected)

student_id  name   email
----------  -----  ---------------------
101         Ava    ava@college.example
102         Ben    ben@college.example
103         Chloe  NULL
104         Dev    dev@college.example
107         Grace  grace@college.example
109         Isla   NULL

Error: UNIQUE constraint failed: student.email

The unique index allows many NULL emails but no two equal non-NULL ones, matching UNIQUE semantics in the standard, where NULLs are not considered equal to each other. SQL Server is an exception that by default allows only one NULL in a unique column. These portability details matter when an application moves between systems.