NOT NULL and DEFAULT

A column without any rule may stay empty: when an INSERT leaves it out, it gets NULL. Sometimes that's right, as with the release date of a game that isn't out yet. But a game without a title or a price makes no sense. NOT NULL after the column's type says that the column must always have a value. This is how the table games of Pixel Shop was created:

CREATE TABLE games (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    genre TEXT NOT NULL,
    price INTEGER NOT NULL,
    released TEXT,
    age_rating INTEGER NOT NULL
) STRICT;

Every column has NOT NULL except released, the only one that may stay empty. (id doesn't need it: a primary key is never NULL.) An INSERT that leaves out a NOT NULL column, or gives it NULL, fails, as you saw in the last lesson:

INSERT INTO games (title, genre, price)
VALUES ('Snow Trails', 'Sports', 20);

Error on line 1: NOT NULL constraint failed: games.age_rating

DEFAULT gives a column the value it gets when an INSERT leaves it out. A table of the subscribers of the shop's newsletter, for example:

CREATE TABLE subscribers (
    id INTEGER PRIMARY KEY,
    email TEXT NOT NULL,
    language TEXT NOT NULL DEFAULT 'en',
    active INTEGER NOT NULL DEFAULT 1
) STRICT;

INSERT INTO subscribers (email)
VALUES ('emma.clark@mail.com');

INSERT INTO subscribers (email, language)
VALUES ('lena.weber@mail.de', 'de');

SELECT * FROM subscribers;

Nobody had to write the language of the first subscriber, or that both subscriptions are active. NOT NULL and DEFAULT go well together: the column always has a value, and an INSERT has to give it only when it's different. You can write the two in either order. The default can be a number, a text in single quotes, or CURRENT_DATE, today's date: a column added TEXT NOT NULL DEFAULT CURRENT_DATE remembers the day each row was inserted.

Typical mistake: expecting DEFAULT to replace NULL. The default is used only when the INSERT leaves the column out. A NULL written in the INSERT stays NULL, and with NOT NULL, it's an error:

INSERT INTO subscribers (email, language)
VALUES ('tom.reed@mail.com', NULL);

Error on line 1: NOT NULL constraint failed: subscribers.language