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.
Return a List of Foreign Keys in SQLite
In SQLite, you can use a PRAGMA statement to return a list of foreign keys for a given table.
What is SQLite?
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.
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.
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.
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.