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.
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.
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 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 readingIn 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.
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 reading