SQLite includes a PRAGMA statement that allows you to check for foreign key violations on a whole database or a given table.
The statement is PRAGMA foreign_key_check, and it works as follows.
Tag: how to
How to Enable/Disable CHECK Constraints in SQLite
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
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
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
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
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
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.
Continue readingHow to Make SQLite’s LIKE Operator Case-Sensitive
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.
Continue reading3 Ways to Replace NULL with “N/A” in SQLite
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.
Continue readingList all Indexes in an SQLite Database
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.