SQLite Table Creation Syntax Explained with Examples

SQLite is a C-language library that implements a small, fast, self-contained, high-reliability, full-featured, SQL database engine. It is a serverless and self-contained database engine preferred for testing, prototype development, and embedded database application development. Creating tables in SQLite is the first step in setting up a database and storing data. This article will guide you through the syntax for creating tables in SQLite by providing clear instructions and detailed code examples.

Basics of Table Creation in SQLite

In SQLite, a CREATE TABLE statement is used to create a new table in the specified database. Here's the basic syntax:

CREATE TABLE table_name (
    column1_name data_type constraints,
    column2_name data_type constraints,
    ...
    columnN_name data_type constraints
);

Let’s break down the SQL CREATE TABLE syntax:

• CREATE TABLE: This keyword indicates that you want to create a new table.
• table_name: The name of the table you wish to create.
• column_name: The name of the column in the table.
• data_type: The data type for the column (such as TEXT, INTEGER, REAL).
• constraints: Optional constraints such as primary key, not null, and unique.

Example of Creating a Simple Table

Let’s create a table named employees with columns for id, name, email, and hire_date.

CREATE TABLE employees (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT UNIQUE,
    hire_date DATE DEFAULT (date('now'))
);

The table defined above has the following characteristics:

• The id column is an integer that automatically increments with each new record.
• The name column is a text field that cannot be null.
• The email column is a text field that must be unique across all records.
• The hire_date column automatically populates with the current date for new rows unless specified otherwise.

Foreign Key Constraints

SQLite also supports foreign key constraints to maintain referential integrity. Suppose you have another table named departments:

CREATE TABLE departments (
    department_id INTEGER PRIMARY KEY AUTOINCREMENT,
    department_name TEXT NOT NULL
);

We can link the employees table to the departments table by adding a department_id column as a foreign key:

CREATE TABLE employees (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT UNIQUE,
    hire_date DATE DEFAULT (date('now')),
    department_id INTEGER,
    FOREIGN KEY(department_id) REFERENCES departments(department_id)
);

In this setup, each employee in the employees table can be assigned to a department in the departments table.

Temporary Tables

Sometimes you may want to store temporary data for immediate use within your session. SQLite allows creating temporary tables that are removed after the session ends:

CREATE TEMPORARY TABLE temp_employee (
    temp_id INTEGER PRIMARY KEY,
    temp_name TEXT
);

This temporary table is useful for situations where persistent storage is not required and for testing purposes.

Conclusion

SQLite offers a simple and effective way to create and manage tables within your database projects. Understanding how to build tables correctly, including managing primary keys, foreign keys, unique fields, and using temporary tables, is crucial in structuring your database efficiently. With these capabilities, SQLite serves as a robust solution for small to medium-sized applications that do not require the overhead of a full-fledged database server.