This page contains a list of aggregate functions that are available in SQLite by default.
Continue readingHow 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.
What is a CHECK Constraint?
In database terms, a CHECK constraint is a type of constraint that checks data before it enters the database.
CHECK constraints help maintain data integrity, because they prevent invalid data entering the database.
Continue readingCreate 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.
Add a Foreign Key to an Existing Table in SQLite
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 readingCreate a Foreign Key in SQLite
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.
Continue readingHow to Enable Foreign Key Support in SQLite
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
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.