The SQLite replace() function enables us to replace one string (or part of a string) with another string.
The way it works is that you provide three arguments; the string that contains the substring to replace, the substring within that string to replace, and the string to replace it with.
Continue readingIn SQLite, you can use the unicode() function to return the unicode code point for a given character.
The way it works is that it returns the unicode code point for the first character of the string that you provide.
Continue readingSQLite 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.
In SQLite, you can use a PRAGMA statement to return a list of foreign keys for a given table.
Continue readingSQLite is a widely-used lightweight, embedded relational database management system (RDBMS).
SQLite is a C-language library that implements a small, fast, self-contained, high-reliability, full-featured, SQL database engine. SQLite is largely SQL standards compliant, and it is fully ACID compliant.
Continue readingThis page contains a list of aggregate functions that are available in SQLite by default.
Continue readingIf 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.
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.
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.
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.