Creating Tables

Prerequisites: Data types

CREATE TABLE defines a new table: its columns, their types, and rules (constraints) the data must follow.

Syntax

CREATE TABLE table_name (
  column_name TYPE constraints,
  ...
);

Example

-- standalone
CREATE TABLE books (
  id        INTEGER PRIMARY KEY,
  title     TEXT NOT NULL,
  author    TEXT NOT NULL,
  year      INTEGER CHECK (year > 0),
  isbn      TEXT UNIQUE,
  in_stock  INTEGER NOT NULL DEFAULT 1
);

INSERT INTO books (title, author, year) VALUES ('Dune', 'Frank Herbert', 1965);
SELECT * FROM books;

Expected output

+----+-------+---------------+------+------+----------+
| id | title |    author     | year | isbn | in_stock |
+----+-------+---------------+------+------+----------+
|  1 | Dune  | Frank Herbert | 1965 | NULL |        1 |
+----+-------+---------------+------+------+----------+

The constraints

PRIMARY KEY — uniquely identifies each row and can't be NULL. In SQLite, an INTEGER PRIMARY KEY fills itself in automatically (PostgreSQL uses GENERATED ALWAYS AS IDENTITY, MySQL AUTO_INCREMENT).
NOT NULL — a value is required.
UNIQUE — no two rows may share the value (multiple NULLs are allowed).
CHECK (condition) — every row must satisfy the condition.
DEFAULT value — used when an insert doesn't supply a value.

Constraints are the database's guard rails: they stop bad data at the door, whatever application sends it.

-- standalone
CREATE TABLE books (id INTEGER PRIMARY KEY, title TEXT NOT NULL, year INTEGER CHECK (year > 0));
INSERT INTO books (title, year) VALUES ('Mystery Book', -5);

Error near line 3: CHECK constraint failed: year > 0

Changing and removing tables

-- standalone
CREATE TABLE books (id INTEGER PRIMARY KEY, title TEXT NOT NULL);
ALTER TABLE books ADD COLUMN pages INTEGER;
ALTER TABLE books RENAME COLUMN title TO book_title;
INSERT INTO books (book_title, pages) VALUES ('Dune', 412);
SELECT * FROM books;
DROP TABLE books;

+----+------------+-------+
| id | book_title | pages |
+----+------------+-------+
|  1 | Dune       |   412 |
+----+------------+-------+

DROP TABLE deletes the table and all its data, permanently. Add IF EXISTS to avoid an error when the table might not exist.

Naming conventions

Use lowercase snake_case for table and column names, and be consistent about plural table names (customers) or singular (customer) — this course uses plural.

Practice

Create a movies table with an auto-numbered id, a required title, a year that must be after 1880, and a rating from 1 to 10.

Show solution

-- standalone
CREATE TABLE movies (
  id     INTEGER PRIMARY KEY,
  title  TEXT NOT NULL,
  year   INTEGER CHECK (year > 1880),
  rating INTEGER CHECK (rating BETWEEN 1 AND 10)
);
INSERT INTO movies (title, year, rating) VALUES ('Arrival', 2016, 9);
SELECT * FROM movies;

+----+---------+------+--------+
| id |  title  | year | rating |
+----+---------+------+--------+
|  1 | Arrival | 2016 |      9 |
+----+---------+------+--------+