DBMS – Page 304

Add a Foreign Key to an Existing Table in SQLite

May 28, 2020May 24, 2020 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.

Create a Foreign Key in SQLite

May 28, 2020May 23, 2020 by Ian

When you create a table in SQLite, you can also create a foreign key in order to establish a relationship with another table.

This article provides an example of creating a foreign key when creating a table in SQLite.

How to Enable Foreign Key Support in SQLite

May 28, 2020May 23, 2020 by Ian

In SQLite, foreign key constraints are not enforced unless foreign key support has been enabled.

Enabling foreign keys involves the following:

Enable foreign keys when compiling SQLite.
Enable foreign keys at runtime.

Restore an SQLite Database

May 28, 2020May 22, 2020 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.

How Group_Concat() Works in SQLite

November 7, 2021May 21, 2020 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.

How Substr() Works in SQLite

March 12, 2022May 21, 2020 by Ian

The SQLite substr() function allows you to return a substring from a string, based on a given starting location within the string.

It requires two arguments, and accepts a third optional argument.

How Round() Works in SQLite

May 28, 2020May 20, 2020 by Ian

In SQLite, the round() function allows you to round numbers up or down to a given decimal place.

It returns a floating-point value from the first argument, with the number of decimal places that you specify in the (optional) second argument.

If you don’t provide the second argument, it’s assumed to be 0.

How SQLite Lower() Works

May 28, 2020May 19, 2020 by Ian

The SQLite lower() function allows you to convert a string to lowercase characters.

More precisely, it returns a copy of its argument, with all ASCII characters converted to lowercase.

How SQLite Upper() Works

May 28, 2020May 19, 2020 by Ian

The SQLite upper() function allows you to convert a string to uppercase characters.

More precisely, it returns a copy of its argument, with all ASCII characters converted to uppercase.

How SQLite Nullif() Works

May 28, 2020May 19, 2020 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.