Category: SQLite

Add a Foreign Key to an Existing Table in SQLite

Posted on by Ian

SQLite supports a very limited subset of the ALTER TABLE statement. The only things you can do with ALTER TABLE in SQLite is rename a table, rename a column within a table, or add a new column to an existing table.

In other words, you can’t use ALTER TABLE to add a foreign key to an existing table like you can in other database management systems.

Therefore, the only way you can “add” a foreign key to an existing table in SQLite is to create a new table with a foreign key, then transfer the data to the new table.

There’s more than one way to do this, but there is a recommended way.

Continue reading →

Restore an SQLite Database

Posted on by Ian

This article explains how to restore an SQLite database from within the SQLite command line interface.

There are a few ways to restore a database from the SQLite CLI.

One way to do it is to simply attach a new database using the backup file (or a copy of it). Another way to restore a database is to use the .restore dot command to restore the database file to your chosen database within SQLite CLI.

Continue reading →

How Group_Concat() Works in SQLite

Posted on by Ian

SQLite has a group_concat() function that allows you to concatenate multiple results returned for a column into one.

This is sometimes referred to as “string aggregation”.

For example, when querying a database, instead of having each column’s value output in a new row, you can use group_concat() to have them output as a comma separated list.

Continue reading →

How SQLite Nullif() Works

Posted on by Ian

The SQLite nullif() function is a handy function that allows you to treat certain values as NULL when required.

It’s not to be confused with the ifnull() function, which does the opposite – enables you to treat NULL values as something else.

The nullif() function accepts two arguments, and returns its first argument if the arguments are different and NULL if the arguments are the same.

Continue reading →