CREATE TABLE

So far we have been using a table I created for you. Now let us learn how to create your own tables. This is how you design the structure that holds your data.

Basic CREATE TABLE

CREATE TABLE employees (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  position TEXT,
  salary REAL,
  hired_date TEXT
);

Each column has a name and a data type. The most common types in SQLite are:

• INTEGER — whole numbers
• REAL — decimal numbers
• TEXT — strings
• BLOB — binary data (images, files)

Adding Constraints

Constraints are rules your data must follow:

CREATE TABLE products (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  product_name TEXT NOT NULL,
  price REAL NOT NULL DEFAULT 0,
  quantity INTEGER DEFAULT 0,
  category TEXT
);

• PRIMARY KEY — unique identifier for each row
• NOT NULL — column cannot be empty
• DEFAULT — value to use if none is provided
• AUTOINCREMENT — automatically assigns increasing numbers

CREATE TABLE IF NOT EXISTS

Trying to create a table that already exists gives an error. To avoid this:

CREATE TABLE IF NOT EXISTS employees (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  department TEXT
);

This is very useful when you are running setup scripts multiple times.