Building tables and changing data · Lesson 13 of 20

CREATE TABLE and constraints

Types, NOT NULL, UNIQUE, CHECK and DEFAULT: a database that protects itself.

10 minutes2 games

CREATE TABLE defines a table's shape: each column's name, its type, and the constraints that stop bad data from getting in at all. They matter more than any validation in your code, because they apply no matter who writes to the table.

SQL

CREATE TABLE badges (
  id INTEGER PRIMARY KEY,
  code TEXT NOT NULL UNIQUE,
  title TEXT NOT NULL,
  points INTEGER NOT NULL DEFAULT 10 CHECK (points > 0),
  created_at TEXT DEFAULT CURRENT_DATE
);

INSERT INTO badges (code, title) VALUES ('first-run', 'First program');
INSERT INTO badges (code, title, points) VALUES ('streak-7', 'Seven-day streak', 50);

SELECT id, code, title, points FROM badges;

Constraint	What it prevents
PRIMARY KEY	a repeated or missing identifier
NOT NULL	an empty value
UNIQUE	two equal values in the column
CHECK (points > 0)	any value failing the condition
DEFAULT 10	prevents nothing; fills in a value when you omit one
REFERENCES t(id)	a reference to a missing row

Try breaking the constraints. Each of these is rejected with a message saying why:

SQL

CREATE TABLE badges (
  id INTEGER PRIMARY KEY,
  code TEXT NOT NULL UNIQUE,
  points INTEGER CHECK (points > 0)
);
INSERT INTO badges (code, points) VALUES ('first-run', 10);

-- This one fails on purpose: try each line on its own too
INSERT INTO badges (code, points) VALUES ('first-run', 20);
-- INSERT INTO badges (code, points) VALUES ('streak', -5);
-- INSERT INTO badges (points) VALUES (5);

Complete the code

The email is required. Complete it so it also can't repeat between users.

CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  email TEXT NOT NULL 
);

Exercise

Create a tasks table with: id (primary key), title text that can't be empty, and done an integer defaulting to 0. Add two tasks, 'Read' and 'Practice' (without giving done), then show the whole table.

Expected result:

id | title | done
1 | Read | 0
2 | Practice | 0

SQL

CREATE TABLE tasks (
  
);

Learn more from

Played 0 of 2 games

SQL