SQLite table creation and constraints

Basics of SQLite

SQLite is a lightweight, serverless database engine that allows for efficient storage and retrieval of data in a single file. It is widely used in applications where simplicity and performance are critical, such as mobile apps, web services, and embedded systems. SQLite supports relational databases, enabling users to create, manage, and query tables with structured data.

Table Creation

Creating a table in SQLite involves using the CREATE TABLE statement. The basic syntax is:

CREATE TABLE table_name (  
    column_name PRIMARY KEY PRIMARY KEY,  
    column_type PRIMARY KEY,  
    ...  
);

Columns define the structure of the table, and their types determine the kind of data they can store. Common data types include TEXT, INTEGER, REAL, BOOLEAN, and DATE.

Columns and Data Types

Columns specify the properties of a table’s data. For example:

TEXT stores strings of text.
INTEGER stores whole numbers.
REAL stores floating-point numbers.
BOOLEAN stores logical values (TRUE or FALSE).

Primary Keys

A primary key uniquely identifies each row in a table. It must be a unique value and not null. To define a primary key:

CREATE TABLE users (  
    id INTEGER PRIMARY KEY AUTOINCREMENT,  
    name TEXT NOT NULL,  
    email TEXT UNIQUE  
);

The AUTOINCREMENT keyword ensures the id column autoincrements, preventing duplicate values.

Constraints

Constraints enforce rules on the data in a table, ensuring data integrity and consistency. SQLite supports several constraint types:

NOT NULL

Enforces that a column cannot have null values.

CREATE TABLE users (  
    id INTEGER PRIMARY KEY AUTOINCREMENT,  
    name TEXT NOT NULL,  
    email TEXT NOT NULL
);

UNIQUE

Ensures that all values in a column are distinct.

CREATE TABLE users (  
    id INTEGER PRIMARY KEY AUTOINCREMENT,  
    name TEXT UNIQUE,  
    email TEXT UNIQUE
);

DEFAULT

Sets a default value for a column if no value is provided.

CREATE TABLE users (  
    id INTEGER PRIMARY KEY AUTOINCREMENT,  
    name TEXT DEFAULT 'Unknown',  
    email TEXT DEFAULT 'noemail@example.com'
);

CHECK

Enforces a condition on a column.

CREATE TABLE users (  
    id INTEGER PRIMARY KEY AUTOINCREMENT,  
    age INTEGER CHECK (age > 0 AND age < 120)
);

FOREIGN KEY

Establishes a relationship between tables by linking a column to another table’s primary key.

CREATE TABLE orders (  
    order_id INTEGER PRIMARY KEY AUTOINCREMENT,  
    user_id INTEGER FOREIGN KEY REFERENCES users(id)
);

Table Management

Managing tables in SQLite involves modifying or deleting tables, as well as handling constraints.

ALTER TABLE

The ALTER TABLE statement allows users to add, modify, or delete columns or constraints.

ALTER TABLE users ADD COLUMN age INTEGER;
ALTER TABLE users DROP COLUMN email;

DROP TABLE

Removes an entire table from the database.

DROP TABLE users;

TRUNCATE TABLE

Deletes all rows from a table without logging the deletion, which is faster than DELETE.

TRUNCATE TABLE users;

Managing Constraints

Constraints can be added or removed using ALTER TABLE. For example:

ALTER TABLE users ADD CONSTRAINT unique_email UNIQUE (email);
ALTER TABLE users DROP CONSTRAINT unique_email;

Practical Considerations

When creating tables, ensure that primary keys are unique and not null, and use appropriate data types. Constraints like UNIQUE and NOT NULL are essential for maintaining data integrity. Tools like SQLite Manager or SQL editors help users visualize and manage tables efficiently.

SQLite’s flexibility in constraint management allows for robust data validation, making it ideal for applications requiring precise data control.