CREATE TABLE: a new table

In short: CREATE TABLE creates a new table with a name, columns and a type for each column, and in SQLite STRICT makes the database enforce those types.

Problem

The suppliers notebook

The bread comes from a bakery, the milk from a dairy, the cleaning products from a wholesale warehouse. Their phone numbers are in a notebook by the till. When the notebook gets lost, nobody knows whom to call.

They belong in the database. Until now the tables were ready-made. This one you will make yourself.

The idea

CREATE TABLE

CREATE TABLE makes a new table: a name, and in brackets the columns, each with a type. The database here is empty:

SQLempty database

CREATE TABLE suppliers (
  id INTEGER PRIMARY KEY,
  name TEXT,
  city TEXT,
  phone TEXT
);
INSERT INTO suppliers (name, city, phone) VALUES ('Zora Bakery', 'Sofia', '02 955 1020');
SELECT * FROM suppliers;

CREATE TABLE suppliers ( id INTEGER PRIMARY KEY, name TEXT, city TEXT, phone TEXT ); INSERT INTO suppliers (name, city, phone) VALUES ('Zora Bakery', 'Sofia', '02 955 1020'); SELECT * FROM suppliers;

▶ RunRestore the queryFresh database

The column id INTEGER PRIMARY KEY is the primary key. In SQLite such a column gets the next number by itself, so it is not in the INSERT. Columns are separated by commas, and there is no comma after the last one.

Here is the new table:

suppliers

• PKidinteger
• nametext
• citytext
• phonetext

More precisely, if you are curious

A table that already exists cannot be made a second time: SQLite stops with “table suppliers already exists”. CREATE TABLE IF NOT EXISTS suppliers (…) makes it only if it is not there. That is handy in a script that runs more than once.

The idea

The types

The type says what values the column will hold. SQLite has five:

• INTEGER: a whole number. Stock, a count, a number.
• REAL: a fractional number. A price, a weight.
• TEXT: text. A name, a city, a phone number.
• BLOB: bytes as they are. A photo, a file.
• NUMERIC: a number that SQLite keeps as a whole number if it can, otherwise as a fractional one.

There is no separate type for dates. The dates in the shop are text in the form '2026-10-25': that way they sort correctly. A phone number is text too, even though it is made of digits: you do not calculate with it, and a leading zero has to stay.

More precisely, if you are curious

REAL is a floating-point number. It keeps most fractional numbers approximately, not exactly, and small errors add up over many calculations. That is why real projects often keep money in cents as INTEGER (250 instead of 2.50) or with the type NUMERIC(10,2) in PostgreSQL and MySQL. In the shop the prices are REAL, so that they read like on a receipt.

Try it

The deliveries

Make a table deliveries for the deliveries: id (primary key), supplier_id (the supplier's number, a whole number), delivered_at (the date) and amount (the sum in euros, a decimal number like the prices in the shop).

SQLempty database

CREATE TABLE deliveries (
  id INTEGER PRIMARY KEY
);

CREATE TABLE deliveries ( id INTEGER PRIMARY KEY );

Check▶ Run

See it

Text in a number column

The storekeeper counts the products and writes down how many there are. One day he writes “twelve” in words. Run the query. typeof() tells you the type of the value that was actually stored:

SQLempty database

CREATE TABLE stock_counts (
  product TEXT,
  counted INTEGER
);
INSERT INTO stock_counts VALUES ('White bread', '12'), ('White cheese', 'twelve');
SELECT product, counted, typeof(counted) FROM stock_counts;

CREATE TABLE stock_counts ( product TEXT, counted INTEGER ); INSERT INTO stock_counts VALUES ('White bread', '12'), ('White cheese', 'twelve'); SELECT product, counted, typeof(counted) FROM stock_counts;

▶ RunRestore the queryFresh database

Both rows go in. '12' becomes the number 12, because the column is INTEGER and SQLite can convert it. “twelve” cannot become a number and stays text in the number column.

This is SQLite's type affinity. The column type is more of a recommendation: SQLite tries to convert the value, but if it cannot, it takes it as it is. The report SUM(counted) will then quietly count “twelve” as 0.

The idea

STRICT

If you write STRICT after the brackets of CREATE TABLE, the types become rules:

SQLempty database

CREATE TABLE stock_counts (
  product TEXT,
  counted INTEGER
) STRICT;
INSERT INTO stock_counts VALUES ('White cheese', 'twelve');

CREATE TABLE stock_counts ( product TEXT, counted INTEGER ) STRICT; INSERT INTO stock_counts VALUES ('White cheese', 'twelve');

▶ RunRestore the queryFresh database

Now “twelve” does not go in. Text that is a number is still accepted and converted:

SQLempty database

CREATE TABLE stock_counts (
  product TEXT,
  counted INTEGER
) STRICT;
INSERT INTO stock_counts VALUES ('White bread', '12');
SELECT product, counted, typeof(counted) FROM stock_counts;

CREATE TABLE stock_counts ( product TEXT, counted INTEGER ) STRICT; INSERT INTO stock_counts VALUES ('White bread', '12'); SELECT product, counted, typeof(counted) FROM stock_counts;

▶ RunRestore the queryFresh database

More precisely, if you are curious

In a STRICT table every column must have a type, and it must be one of INTEGER (or INT), REAL, TEXT, BLOB and ANY. ANY takes anything without converting it. NUMERIC is not allowed there. STRICT tables have been in SQLite since version 3.37 (2021). The course runs on SQLite 3.49.

Try it

The strict stock count

The stock count table has a product (product, text), the counted amount (counted, a whole number) and a date (counted_at). Make it so that words instead of numbers do not get in.

SQLempty database

CREATE TABLE stock_counts (
  product TEXT,
  counted INTEGER,
  counted_at TEXT
);

CREATE TABLE stock_counts ( product TEXT, counted INTEGER, counted_at TEXT );

Check▶ Run

The idea

In PostgreSQL and MySQL

The big databases have more types. You will meet them in almost every schema:

• VARCHAR(50): text of up to 50 characters.
• NUMERIC(10,2): an exact number with 2 digits after the decimal point. For money.
• DATE and TIMESTAMP: a date, and a date with a time.
• BOOLEAN: true or false.

SQLite accepts these names, but does not enforce them:

SQLempty database

CREATE TABLE promo_codes (
  code VARCHAR(5),
  active BOOLEAN,
  valid_until DATE
);
INSERT INTO promo_codes VALUES ('AUTUMN-2026-FOR-ALL', 'yes', 'by Christmas');
SELECT code, typeof(code), active, typeof(active), valid_until FROM promo_codes;

CREATE TABLE promo_codes ( code VARCHAR(5), active BOOLEAN, valid_until DATE ); INSERT INTO promo_codes VALUES ('AUTUMN-2026-FOR-ALL', 'yes', 'by Christmas'); SELECT code, typeof(code), active, typeof(active), valid_until FROM promo_codes;

▶ RunRestore the queryFresh database

The code is longer than 5 characters, “yes” is not a boolean, “by Christmas” is not a date. SQLite stores them as text. PostgreSQL would reject all three. When you write a schema for another database, use its types. When you try it in SQLite, do not count on them to protect the data.

Exercise

The suppliers in the shop

Next to the shop's tables, make suppliers with id (primary key), name and city. Add two suppliers: “Zora Bakery” from Sofia and “Balkan Dairy” from Smolyan.

SQLdatabase: shop

Check▶ Run

Test

Check yourself

1. Which type fits a price like 2.50 €?

REALINTEGERBLOB

2. The table is not STRICT, and the column counted is INTEGER. What happens when you INSERT the text “twelve”?

0 is storedIt is stored as textAn error

3. How are dates kept in SQLite?

As text in the form '2026-10-25'In a separate DATE type, as in PostgreSQLOnly as a number

4. What does STRICT at the end of CREATE TABLE do?

It makes SQLite reject a value of the wrong typeIt makes the table read-onlyIt forbids empty values

Check

Start the lesson over

Related lessons

• Builds on: SELECT: choosing columns · Aggregate functions: COUNT, SUM, AVG · Primary key and foreign key · INSERT: new rows
• Continues in: Constraints: rules for the data · Foreign keys: links between tables · ALTER TABLE: changing a table · Views
• Tasks on this topic: Tables and rules