Master SQLite Table Creation, Alteration, and Deletion

As an embedded database engine, SQLite is one of the most versatile, lightweight options for local data storage. Whether you‘re developing a desktop application, mobile app, IoT device, or simply need an ad-hoc database for data analysis, knowing how to properly create, modify, and delete SQLite tables is essential.

In this comprehensive guide, we‘ll explore the various SQLite commands that allow you to build and evolve your database schema.

An Overview of Creating, Altering, and Dropping Tables

When first designing a SQLite database, you need to manually create tables that will hold your data. This is done with the CREATE TABLE statement. An example basic syntax:

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

This creates a new table called mytable with columns for ID, name, and age. Primary keys, data types, and other constraints can be defined at creation time.

Once you start adding data and evolving your application, chances are you‘ll need to modify your initial table design. SQLite provides an ALTER TABLE command just for this purpose. You may want to:

  • Add, remove, or rename columns
  • Add a primary key to an existing column
  • Change a column‘s data type
  • Add indexes, triggers, constraints

Altering an existing table saves you from having to delete and re-create it from scratch.

Finally, if you decide a table no longer serves your application needs, you can permanently get rid of it with the DROP TABLE statement. For example:

DROP TABLE mytable;

This deletes the table mytable and all its data from the database!

As you can see, SQLite gives you considerable flexibility to update your tables on the fly. Now let‘s look at each of these commands in more detail.

Creating New SQLite Tables with CREATE TABLE

Purposefully designing your tables upfront is ideal before you start collecting and storing data. The CREATE TABLE statement allows you to define:

  • The table‘s name
  • Its column names and data types for those columns
  • Any constraints or requirements on each column

For example:

CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  first_name TEXT NOT NULL,
  last_name TEXT NOT NULL,
  email TEXT UNIQUE, 
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

Here we define a users table with columns for id, first_name, last_name, email, and created_at timestamp.

We also set the following column constraints:

  • id as the primary key
  • first_name and last_name as NOT NULL, disallowing null values
  • email as UNIQUE, ensuring no duplicate email addresses
  • created_at defaulting to the current timestamp if not provided

Properly defining columns and constraints upfront is crucial to ensuring data integrity as your app scales.

As you can see, SQLite supports the usual primitive data types like INTEGER, TEXT, DATETIME, etc. But when should you use which?

Data Type Use Cases
INTEGER Integer numbers – use different sizes to save space
TEXT Character strings, names, text blobs. Configurable lengths
DATETIME Timestamps, log events data
REAL Floating point values, decimals

Now let‘s talk about primary keys…

The Role of Primary Keys in SQLite Tables

A primary key is a column that uniquely identifies each row in your table. SQLite will enforce this uniqueness for you automatically.

Primary keys are also always indexed making lookups very fast. Good primary key choices:

  • Auto-incrementing IDs – no thinking required!
  • Stable values like email if guaranteed unique
  • Combinations of columns

Syntax is simple, just append PRIMARY KEY when defining it in your CREATE TABLE:

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

Here id is the primary key. email has a unique constraint but is not the PK.

According to a 2020 StackOverflow survey, over 60% of respondents favored auto-incrementing IDs over "natural" keys. The simplicity is hard to beat!

Now let‘s discuss modifying existing tables…

Altering Tables with ALTER TABLE

So you‘ve already created your production tables. Your app takes off and now you need to modify your database schema – adapt it to new requirements. This is where SQLite‘s ALTER TABLE command shines.

You can modify existing tables without needing to rebuild them by hand or migrate all the data. Some examples of alterations:

Add New Columns

-- Add a new column 
ALTER TABLE users ADD COLUMN phone_number TEXT;

Easy! Just specify the table name, the ADD COLUMN clauses, and the new column definition.

Rename Existing Columns

Maybe you want to rename a column for clarity:

-- Change column name
ALTER TABLE users RENAME COLUMN phone_number TO phone;

Simple name change. The actual column data remains intact.

Change Data Types

What if you want to expand a column‘s length or support decimals? 

-- Update email length  
ALTER TABLE users ALTER COLUMN email TYPE VARCHAR(320);

-- Allow decimals  
ALTER TABLE inventory ALTER COLUMN cost TYPE DECIMAL(10,2);

Altering the column data type allows you to evolve the allowed data without disruption.

As you can see, ALTER TABLE is quite flexible in adapting tables to changing needs! SQLite experts recommend relying on alterations over dropping and recreating tables in many cases.

Now what about deleting tables completely when necessary?

Permanently Deleting Tables with DROP TABLE

While altering tables allows non-destructive changes, sometimes a table has simply outlived its purpose. Or you may want to recreate it from scratch with an improved design.

SQLite lets you permanently delete or "drop" tables with the DROP TABLE command: 

DROP TABLE users;

And it‘s gone! Be very careful with this one, as it also permanently deletes all the table‘s data and indexes from the database.

If the users table had 1 million rows of user accounts – poof! Those are gone unless you backed up the table elsewhere first.

Some best practices around dropping tables:

  • Always back up a table first before dropping – either by exporting the data or creating a duplicate copy of the table 
-- Duplicate
CREATE TABLE users_backup AS SELECT * FROM users;

  • Double check you are targeting the correct table before running DROP! Caps lock problems have claimed many tables before.

  • Consider temporarily renaming a table instead of fully dropping if you think you may need the data again later.

  • Note that foreign key constraints can block dropping tables that are still being referenced elsewhere. You may need to update other tables first to remove dependencies before the target table can be dropped.

Comparing SQLite Table Alteration to Other Databases

SQLite shares many similarities with larger database platforms with regards to create, alter, drop table functionality:

  • MySQL, Postgres, SQL Server all allow flexible, online table alterations without locking the table for reads/writes.
  • Syntax varies slightly – MySQL uses MODIFY COLUMN while SQLite uses ALTER COLUMN for example.
  • Advanced index changes can be more complex on other DBs – SQLite keeps things simple.
  • Data type support differs across DBs – maisnstrengths of SQLite are JSON support, flexible typing.

However, there are also several advantages that set SQLite apart for local, embedded use cases:

  • No separate database server – SQLite runs in the app process instead of a background service
  • Zero configuration – No complex installation/setup, works out of the box
  • Has to ship with the app – ideal for apps that need embedded data stores
  • Simplified admin and tuning – Less to monitor and optimize compared to client/server DBs

So while knowledge of ANSI SQL carries over, SQLite distinguishes itself through simplicity. The create/alter/drop table commands reflect this philosophy.

Key Takeaways and Next Steps

The CREATE TABLE, ALTER TABLE, and DROP TABLE commands give you extensive flexibility to evolve your SQLite databases on-the-fly. Following best practices around indexes, data types, constraints, and backing up data will ensure you avoid pitfalls.

For next steps, consider exploring:

  • Additional SQLite commands like temporary tables, triggers, views
  • Data type optimizations like reducing storage size
  • Importing/exporting data from JSON, CSV or other formats
  • Other embedded databases like MySQL or Postgres for comparison

With SQLite‘s resilient table management and flat, portable data storage you can build stable systems quickly. Yet you retain the power to adapt schemas painlessly as needs change.

Let me know in the comments if this guide helped explain SQLite‘s take on creating, altering and removing tables. I‘m happy to chat more!

Read More Topics