>_ IzzyLab

Hands-on task

This task is step 9 of the course “Your first database”

CREATE TABLE: a suppliers table

easy~6 min

The shop needs a suppliers table. Create a suppliers table in the /root/shop.db database with three columns:

  • id — a whole number, the primary key (the row's number, the database assigns it itself);
  • name — text;
  • phone — text.

Then add the first supplier to it: name Kitchen-Pro, phone +15550101234.

What you'll practise:
  • sqlite3
  • create table
  • column types
  • primary key

Topic breakdown

A new table is created with CREATE TABLE. In the brackets you list the columns: name and type separated by a space, columns separated by commas:

CREATE TABLE readers (
  id    INTEGER PRIMARY KEY,
  name  TEXT,
  age   INTEGER
);

The main SQLite types: INTEGER — whole numbers, REAL — fractional numbers, TEXT — text. A phone number is stored as text: it can contain + and leading zeros, and nobody is going to do arithmetic with it.

PRIMARY KEY is the primary key: the column that identifies each row unambiguously. For an INTEGER PRIMARY KEY, SQLite assigns the numbers itself on INSERT.

If a table with that name already exists, CREATE TABLE gives the error table … already exists. To delete a whole table — DROP TABLE name.

What's checked

  1. Table suppliers created
  2. The table has the columns id, name and phone
  3. The columns have the right types, id is the primary key
  4. The supplier is added

What it looks like