how to – Page 171

How to Enable/Disable CHECK Constraints in SQLite

August 9, 2024May 27, 2020 by Ian

If you ever need to enable or disable all CHECK constraints in SQLite, you can use the ignore_check_constraints PRAGMA statement.

This pragma statement explicitly enables or disables the enforcement of CHECK constraints. The default setting is off, meaning that CHECK constraints are enforced by default.

Create a CHECK Constraint in SQLite

August 9, 2024May 26, 2020 by Ian

In SQLite, you can create a CHECK constraint by adding the applicable code within the CREATE TABLE statement when creating the table.

If a table has a CHECK constraint on it, and you try to insert or update data that violates the CHECK constraint, the operation will fail with an error.

Create an Auto-Increment Column in SQLite

May 28, 2020May 26, 2020 by Ian

SQLite has an interesting way of handling auto-increment columns. By auto-incrementing columns, I mean columns that increment automatically whenever new data is inserted.

This is similar to an IDENTITY column in SQL Server or an AUTO_INCREMENT column in MySQL.

This article explains how to create AUTOINCREMENT columns in SQLite.

How to Show the Current Dot Command Settings in SQLite

May 28, 2020May 25, 2020 by Ian

Sometimes you might want to check your “dot command” existing settings in the SQLite command line interface.

Fortunately there’s a .show dot command that returns various settings and their current values.

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 to Change the Command Line Prompt in SQLite

May 28, 2020May 16, 2020 by Ian

If you’ve ever used the SQLite command line shell, you’re probably familiar with the default command line prompt. Actually, there are two prompts:

The default main prompt looks like this: sqlite>
The default continuation prompt looks like this: ...>

If you don’t like these prompts, you can always change them with the .prompt dot command.

This article provides a quick demonstration on how to change these prompts.

How to Make SQLite’s LIKE Operator Case-Sensitive

May 28, 2020May 15, 2020 by Ian

By default, the SQLite LIKE operator is case-insensitive for ASCII characters. This means it will match uppercase and lowercase characters, regardless of which case you use in your pattern.

However, there is a technique you can use to make it case-sensitive.

3 Ways to Replace NULL with “N/A” in SQLite

May 28, 2020May 13, 2020 by Ian

In SQLite, if you need to replace NULL results with text such as “N/A”, “None”, or even the text “NULL”, you can use one of the three solutions below.

List all Indexes in an SQLite Database

August 5, 2024May 13, 2020 by Ian

In this article I outline two ways to return a list of indexes in an SQLite database.

The first (and most obvious) method is to use the .indexes dot command. The second method is to query the sql_master table.

Tweak your Avg() Results in SQLite with the DISTINCT Keyword

May 28, 2020May 11, 2020 by Ian

If you know about the avg() function in SQLite, you’re probably aware that it returns the average of all non-NULL X within a group.

But did you know you can add the DISTINCT keyword to this function?

If you add the DISTINCT keyword, avg() will calculate its results based on distinct values only. This is essentially the same as removing duplicate values and then calculating the average on the remaining values.