Lesson 13 / 18

Creating Tables

The parts of a column definition, column-level and table-level constraints, how keys are expressed in a schema, and the error messages a constraint violation produces in the engine.

Contents

The course so far has been about reading data that already exists: selecting columns, writing conditions, joining tables, grouping rows, and combining result sets with set operations. Every query took an already-built schema as a given. This lesson removes that assumption and asks the question that comes next: how is the schema itself written.

The first of the families separated out in the SQL Language Families lesson — data definition language — is this topic’s territory. The library schema was set up in that lesson, and three kinds of constraint were introduced there: PRIMARY KEY, NOT NULL, and REFERENCES. That lesson explained what these declarations say; this one shows what they prevent, and adds to the list what lies beyond the three — default values, value-range checks, uniqueness, and named constraints.

A table definition sets the rule for which rows may enter that table. If the rule lives in the schema, the engine enforces it on every write attempt; if the rule lives only in application code, every path that bypasses that code punches through it. This lesson’s subject is that difference.

Parts of a Table Definition

The body of a CREATE TABLE statement is a comma-separated list of items. Each item is either a column definition or a table constraint. A column definition has three parts: a name, a type, and zero or more constraints.

The smallest table in the schema holds branches.

sqlite3 :memory: <<'SQL'
.headers on
.mode column
CREATE TABLE branch (
  branch_id INTEGER PRIMARY KEY,
  name      TEXT NOT NULL,
  city      TEXT NOT NULL
);

INSERT INTO branch VALUES (1,'Central','Ankara'),(2,'Bahcelievler','Ankara');
INSERT INTO branch (branch_id, name, city) VALUES (3,'Kadikoy','Istanbul');

SELECT * FROM branch;
SQL

branch_id  name          city    
---------  ------------  --------
1          Central       Ankara  
2          Bahcelievler  Ankara  
3          Kadikoy       Istanbul

The :memory: argument on the command line builds the database in memory rather than on disk: the moment the example ends, it disappears. Every block in this lesson runs this way and leaves no file behind. The library.db file from the querying lessons is left untouched.

Forbidding Blanks and Setting a Default

The Data Modeling and Relational Theory course showed that a null value means “unknown” and triggers three-valued logic. When a blank makes no sense for a column, the schema says so: NOT NULL.

DEFAULT specifies what a column is filled with when an insert leaves it unspecified. UNIQUE requires that the values in a column never repeat. The member table uses all three. Two things change from the definition used in the querying lessons: email must now be unique, and a column holding membership status has been added.

sqlite3 :memory: <<'SQL'
.headers on
.mode column
CREATE TABLE member (
  member_id         INTEGER PRIMARY KEY,
  first_name        TEXT NOT NULL,
  last_name         TEXT NOT NULL,
  email             TEXT UNIQUE,
  registered_at TEXT NOT NULL,
  status            TEXT NOT NULL DEFAULT 'active'
);

INSERT INTO member (member_id, first_name, last_name, email, registered_at)
VALUES (1,'Alice','Kane','[email protected]','2023-02-14');
INSERT INTO member (member_id, first_name, last_name, email, registered_at)
VALUES (2,'Ben',NULL,'[email protected]','2023-05-30');
INSERT INTO member (member_id, first_name, last_name, email, registered_at)
VALUES (3,'Clara','Diaz','[email protected]','2024-01-09');

SELECT * FROM member;
SQL

Runtime error near line 14: NOT NULL constraint failed: member.last_name (19)
Runtime error near line 16: UNIQUE constraint failed: member.email (19)
member_id  first_name  last_name  email               registered_at  status
---------  ----------  ---------  ------------------  -------------  ------
1          Alice       Kane       [email protected]  2023-02-14     active

Three inserts were attempted, one succeeded. The first row got the value active for status even though it never wrote to that column: the default kicked in. The second left the last name blank and ran into the NOT NULL constraint. The third repeated an email address that was already in use.

The structure of the error message matters: which kind of constraint, which table, which column. On a constraint violation the engine does not write the row; the statement fails and its effect is undone in full. There is no such thing as a partially written row.

Uniqueness and Null Values

PRIMARY KEY is the schema-level counterpart of the primary key defined in the Relational Theory course, and it is a combination of two constraints: uniqueness plus not being null. UNIQUE asks for only the first of the two.

A null value is what makes the difference visible. In standard SQL, a unique column can hold more than one null, because two unknown values cannot be said to be equal. The email column in the schema is an example: some members have no email on file.

sqlite3 :memory: <<'SQL'
.headers on
.mode column
CREATE TABLE member (
  member_id         INTEGER PRIMARY KEY,
  first_name        TEXT NOT NULL,
  last_name         TEXT NOT NULL,
  email             TEXT UNIQUE,
  registered_at TEXT NOT NULL
);

INSERT INTO member VALUES (3,'Clara','Diaz',NULL,'2024-01-09');
INSERT INTO member VALUES (5,'Grace','Kim',NULL,'2024-11-05');
INSERT INTO member VALUES (6,'Owen','Park','[email protected]','2025-01-18');

SELECT count(*) AS rows, count(email) AS with_email FROM member;
SQL

rows  with_email
----  ----------
3     1

Two rows stood with a blank email at the same time. This behavior is a point where engines can differ: some accept more than one null, others offer additional syntax that leaves the decision to the designer. When a column needs to be both unique and mandatory, the pair NOT NULL UNIQUE is written — which is exactly the definition of a primary key.

A key can also be made up of more than one column. The rule that stops a member from putting the same book on hold twice is a composite primary key written at the table level.

sqlite3 :memory: <<'SQL'
.headers on
.mode column
CREATE TABLE reservation (
  member_id INTEGER NOT NULL,
  book_id   INTEGER NOT NULL,
  date      TEXT    NOT NULL,
  PRIMARY KEY (member_id, book_id)
);

INSERT INTO reservation VALUES (1, 5, '2025-07-01');
INSERT INTO reservation VALUES (2, 5, '2025-07-02');
INSERT INTO reservation VALUES (1, 6, '2025-07-03');
INSERT INTO reservation VALUES (1, 5, '2025-07-04');

SELECT * FROM reservation;
SQL

Runtime error near line 13: UNIQUE constraint failed: reservation.member_id, reservation.book_id (19)
member_id  book_id  date      
---------  -------  ----------
1          5        2025-07-01
2          5        2025-07-02
1          6        2025-07-03

Different members could hold the same book, and the same member could hold different books; the only thing that repeated was the pair itself. In a composite key, uniqueness applies to the value the columns form together, not to each column on its own.

Narrowing the Value Range

CHECK writes a condition that gets validated when a row is written. If the condition evaluates to false, the row is rejected. A rule such as membership status being one of three values gets moved into the schema this way, rather than living in application code.

sqlite3 :memory: <<'SQL'
.headers on
.mode column
CREATE TABLE member (
  member_id         INTEGER PRIMARY KEY,
  first_name        TEXT NOT NULL,
  last_name         TEXT NOT NULL,
  registered_at TEXT NOT NULL,
  status             TEXT NOT NULL DEFAULT 'active'
                 CHECK (status IN ('active', 'suspended', 'closed'))
);

INSERT INTO member (member_id, first_name, last_name, registered_at, status)
VALUES (1,'Alice','Kane','2023-02-14','suspended');
INSERT INTO member (member_id, first_name, last_name, registered_at, status)
VALUES (2,'Ben','Ortiz','2023-05-30','inactive');

SELECT * FROM member;
SQL

Runtime error near line 14: CHECK constraint failed: status IN ('active', 'suspended', 'closed') (19)
member_id  first_name  last_name  registered_at  status   
---------  ----------  ---------  -------------  ---------
1          Alice       Kane       2023-02-14     suspended

A check constraint written inside a column definition can only look at that column. When a rule needs to test two columns together, the constraint is written at the end of the column list as a table constraint. The prefix CONSTRAINT name gives the constraint a name; that name shows up in the error message and makes it readable which rule the error belongs to.

sqlite3 :memory: <<'SQL'
.headers on
.mode column
CREATE TABLE loan (
  loan_id       INTEGER PRIMARY KEY,
  book_id       INTEGER NOT NULL,
  member_id     INTEGER NOT NULL,
  pickup_date   TEXT    NOT NULL,
  return_date   TEXT,
  CONSTRAINT return_after_checkout
    CHECK (return_date IS NULL OR return_date >= pickup_date)
);

INSERT INTO loan VALUES (1, 1, 1, '2025-01-10', '2025-01-24');
INSERT INTO loan VALUES (3, 1, 2, '2025-02-11', NULL);
INSERT INTO loan VALUES (4, 3, 3, '2025-03-01', '2025-02-15');

SELECT * FROM loan;
SQL

Runtime error near line 15: CHECK constraint failed: return_after_checkout (19)
loan_id  book_id  member_id  pickup_date  return_date
-------  -------  ---------  -----------  -----------
1        1        1          2025-01-10   2025-01-24 
3        1        2          2025-02-11

The return_date IS NULL OR part of the condition cannot be left out. A comparison against a null value evaluates to unknown rather than true; if the constraint consisted only of the second condition, loans that had not yet been returned would be rejected too. The three-valued logic of null values is the most common place to stumble when writing a constraint.

Links Between Tables

The REFERENCES syntax requires that a value in one column be found among the keys of another table. This is referential integrity’s expression in the schema: a book cannot be written against a branch that does not exist.

Whether foreign key checking is switched on differs from engine to engine. In the command-line tool used here, the check is off by default and is turned on per connection.

sqlite3 :memory: <<'SQL'
.headers on
.mode column
CREATE TABLE branch (branch_id INTEGER PRIMARY KEY, name TEXT NOT NULL, city TEXT NOT NULL);
CREATE TABLE book (
  book_id          INTEGER PRIMARY KEY,
  title            TEXT NOT NULL,
  author           TEXT NOT NULL,
  publication_year INTEGER CHECK (publication_year BETWEEN 1450 AND 2100),
  branch_id        INTEGER REFERENCES branch(branch_id)
);

INSERT INTO branch VALUES (1,'Central','Ankara');
INSERT INTO book VALUES (1,'Blindness','José Saramago',1995,9);

SELECT * FROM book;
SQL

book_id  title      author         publication_year  branch_id
-------  ---------  -------------  ----------------  ---------
1        Blindness  José Saramago  1995              9

The constraint written into the schema was not enforced; a row carrying a branch number that does not exist entered the table. When the same block is opened with a PRAGMA foreign_keys = ON; line, the result changes.

sqlite3 :memory: <<'SQL'
.headers on
.mode column
PRAGMA foreign_keys = ON;

CREATE TABLE branch (branch_id INTEGER PRIMARY KEY, name TEXT NOT NULL, city TEXT NOT NULL);
CREATE TABLE book (
  book_id          INTEGER PRIMARY KEY,
  title            TEXT NOT NULL,
  author           TEXT NOT NULL,
  publication_year INTEGER CHECK (publication_year BETWEEN 1450 AND 2100),
  branch_id        INTEGER REFERENCES branch(branch_id)
);

INSERT INTO branch VALUES (1,'Central','Ankara');
INSERT INTO book VALUES (1,'Blindness','José Saramago',1995,9);
INSERT INTO book VALUES (2,'The Disconnected','Oğuz Atay',1972,1);
INSERT INTO book VALUES (7,'Tehlikeli Oyunlar','Oğuz Atay',1973,NULL);

SELECT * FROM book;
SQL

Runtime error near line 15: FOREIGN KEY constraint failed (19)
book_id  title              author     publication_year  branch_id
-------  -----------------  ---------  ----------------  ---------
2        The Disconnected   Oğuz Atay  1972              1        
7        Tehlikeli Oyunlar  Oğuz Atay  1973

The book linked to a branch that does not exist was rejected, and the one linked to a valid branch was accepted. The third row’s branch number is null, and that one was accepted too: a foreign key does not check when the column is null — “which branch it is in is unknown” is different from “in a branch that does not exist.”

The PRAGMA syntax is not part of standard SQL; it belongs to the engine in use. The lesson that transfers is this: a constraint being written into the schema does not mean it is being checked. When starting work with a new database, the state of foreign key checking is tested; a check left off means orphan rows discovered months later.

The Library Schema

Put together, this lesson’s pieces form the schema the rest of the topic uses.

sqlite3 :memory: <<'SQL'
.headers on
.mode column
PRAGMA foreign_keys = ON;

CREATE TABLE branch (
  branch_id INTEGER PRIMARY KEY,
  name      TEXT NOT NULL,
  city      TEXT NOT NULL
);

CREATE TABLE book (
  book_id          INTEGER PRIMARY KEY,
  title            TEXT    NOT NULL,
  author           TEXT    NOT NULL,
  publication_year INTEGER CHECK (publication_year BETWEEN 1450 AND 2100),
  branch_id        INTEGER REFERENCES branch(branch_id)
);

CREATE TABLE member (
  member_id         INTEGER PRIMARY KEY,
  first_name        TEXT NOT NULL,
  last_name         TEXT NOT NULL,
  email             TEXT UNIQUE,
  registered_at TEXT NOT NULL,
  status            TEXT NOT NULL DEFAULT 'active'
                 CHECK (status IN ('active', 'suspended', 'closed'))
);

CREATE TABLE loan (
  loan_id       INTEGER PRIMARY KEY,
  book_id       INTEGER NOT NULL REFERENCES book(book_id),
  member_id     INTEGER NOT NULL REFERENCES member(member_id),
  pickup_date TEXT    NOT NULL,
  return_date   TEXT,
  CONSTRAINT return_after_checkout
    CHECK (return_date IS NULL OR return_date >= pickup_date)
);

INSERT INTO branch VALUES (1,'Central','Ankara'),(3,'Kadikoy','Istanbul');
INSERT INTO book VALUES (1,'Blindness','José Saramago',1995,1),(5,'Silent House','Orhan Pamuk',1983,3);
INSERT INTO member (member_id, first_name, last_name, email, registered_at)
VALUES (1,'Alice','Kane','[email protected]','2023-02-14'),(3,'Clara','Diaz',NULL,'2024-01-09');
INSERT INTO loan VALUES (1,1,1,'2025-01-10','2025-01-24'),(4,5,3,'2025-03-01',NULL);

SELECT u.first_name, u.last_name, k.title, s.name AS branch, o.pickup_date
FROM loan AS o
JOIN member AS u ON u.member_id = o.member_id
JOIN book   AS k ON k.book_id   = o.book_id
JOIN branch AS s ON s.branch_id = k.branch_id
ORDER BY o.loan_id;
SQL

first_name  last_name  title         branch   pickup_date
----------  ---------  ------------  -------  -----------
Alice       Kane       Blindness     Central  2025-01-10 
Clara       Diaz       Silent House  Kadikoy  2025-03-01

All four tables have a primary key that is an integer not drawn from the data itself. Books had a natural candidate — the ISBN — but that number names an edition; two copies of the same edition carry the same number, and the key cannot tell the copies apart. A natural field that looks like a key but cannot serve as one is a situation that comes up often in schema design, and it is usually resolved with a generated key like the one in this lesson.

Summary

A column definition consists of a name, a type, and constraints; if a constraint lives in the schema the engine enforces it on every write path, and if it lives in application code it is enforced only on the path that goes through that code.
NOT NULL forbids blanks, DEFAULT fills in a column that was not written, and CHECK narrows the range of values; on a column that can be null, the check condition must handle the blank separately.
UNIQUE asks for uniqueness alone, PRIMARY KEY asks for uniqueness together with being mandatory; a key can be made up of more than one column, and uniqueness applies to the value those columns produce together.
REFERENCES writes referential integrity into the schema, does not check on a null column, and whether the check is switched on differs from engine to engine.
On a constraint violation the statement fails in full; because the error message gives the constraint type, the table, and the constraint name, the diagnosis can be read directly.

Next Step

A schema does not stay in the shape it was first written: a new column becomes necessary, a column’s name turns out to have been chosen badly, a constraint needs to be added after the fact. The next lesson takes up altering the schema and shows the real difficulty: the changes ALTER TABLE can make in a single statement are limited, and that limit differs between engines. Past the limit, the table gets rebuilt; that lesson also sets up the portable, data-preserving form of that rebuild.

To keep your progress and take notes, Log in