Creating a Table

Until now, you've worked with tables that were already there. In this lesson, you'll create tables of your own, and on the way, you'll see how the tables of Pixel Shop were made. A new table is created by the statement CREATE TABLE, followed by the table's name and, in brackets, its columns:

CREATE TABLE platforms (
    name TEXT,
    maker TEXT,
    year INTEGER
) STRICT;

Each column has a name and a type, which says what kind of values it holds: TEXT for text, INTEGER for whole numbers. You'll meet the other types in the next exercise. Commas separate the columns, and after the closing bracket come the word STRICT and a semicolon. Writing each column on a line of its own isn't required, but it makes the table easy to read.

Names of tables and columns are usually written in lowercase, with an underscore between words, like order_items or age_rating. A table usually gets a name in the plural, as it holds many rows: games, customers.

CREATE TABLE doesn't look for any rows, so it shows no result, and the new table is empty. You fill it with INSERT, which you know from the last lesson, and look at it with SELECT:

INSERT INTO platforms (name, maker, year)
VALUES ('PlayStation 5', 'Sony', 2020),
       ('Switch', 'Nintendo', 2017),
       ('Xbox Series X', 'Microsoft', 2020);

SELECT * FROM platforms;

This exercise starts with an empty database, so its Tables tab has nothing to show. It wouldn't show your table anyway: it shows only the tables an exercise starts with. To see what's in your table, query it. And as every Run starts with a fresh database, your file creates the table anew each time. In a real database, a table is created once and then stays; running the same CREATE TABLE again would end with the error table platforms already exists.

What is STRICT for? Without it, SQLite lets any value into any column, even a text into a column of whole numbers. SQLite is unusual in this: other databases, such as PostgreSQL or MySQL, always check the types. STRICT makes SQLite check them too, so a value of the wrong type is refused:

INSERT INTO platforms (name, maker, year)
VALUES ('Steam Deck', 'Valve', 'in 2022');

Error on line 1: cannot store TEXT value in INTEGER column platforms.year

All tables of Pixel Shop are STRICT, and so will be every table you create in this course. A mistake in the data is easy to fix when the database stops it right away, and much harder when someone finds it months later.

Typical mistake: a comma after the last column. Commas go only between the columns, so this fails:

CREATE TABLE platforms (
    name TEXT,
    maker TEXT,
    year INTEGER,
) STRICT;

Error on line 1: near ")": syntax error