When creating a table in SQLite, you have the option of adding constraints to each column.
One such constraint is the DEFAULT constraint.
The DEFAULT constraint allows you to specify a value to be used in the event no value is supplied for that column when a new row is inserted.
If you don’t use a DEFAULT clause, then the default value for a column is NULL.
You can add a generated column to an existing table in SQLite by using the ALTER TABLE statement.
SQLite’s implementation of the ALTER TABLE statement is very limited, but it does allow you to add a column – including generated columns.
Generated columns (also known as “computed columns”) are columns that get their value from an expression that computes values from other columns.
Continue readingGenerated column support was added to SQLite in version 3.31.0, which was released on 22 January 2020.
Generated columns and computed columns are the same thing. They are columns whose values are a function of other columns in the same row.
In SQLite, generated columns are created using the GENERATED ALWAYS column-constraint when creating or altering the table.
There are two types of generated column; STORED and VIRTUAL. Only VIRTUAL columns can be added when altering a table. Both types can be added when creating a table.
In SQLite, you can use the ALTER TABLE statement to add a column to an existing table.
This is actually one of the few things you can do with the ALTER TABLE statement in SQLite. The only things you can do with the ALTER TABLE statement in SQLite is rename a table, rename a column, and add a new column to an existing table.
One of SQLite‘s non-standard extensions to SQL is the ON CONFLICT clause.
This clause allows you to determine what should happen when certain conflicts occur due to a constraint violation.
One of the things you can use this clause for is to replace NULL values with a column’s default value when inserting or updating data in a table.
By default, if you try to explicitly insert NULL into a column with a NOT NULL constraint, it will fail.
And if you try to explicitly insert NULL into a column without a NOT NULL constraint, then NULL will be assigned to that column, even if there’s a DEFAULT clause.
However, you can use the ON CONFLICT clause to set it to the default value instead of NULL.
In SQLite, when you try to insert multiple rows into a table, and any of those rows violates a constraint on that table, the operation will fail.
This is to be expected, after all, that’s what the constraint is for.
But what if you just want to ignore any rows that violate constraints? In other words, if a row violates a constraint, you want SQLite to skip that row, then carry on processing the next row, and so on.
Fortunately, there’s an easy way to do this in SQLite.
Continue readingSometimes you just want to see the basic structure of a table.
In SQLite, there are several ways to retrieve information about a given table. In particular, here are four options:
PRAGMA table_info() statementPRAGMA table_xinfo() statement (for virtual tables).schema commandsqlite_master tableExamples of each method are below.
Continue readingWhen it comes to backing up your databases in SQLite, you have a few options as to how to go about it.
In particular, you can use one of the following methods:
.backup command to back up a specified database.dump command to export the database to a .sql file.clone command to clone the databaseThis article presents two ways to insert a new line character into a string in SQLite.
This means you can have some text on one line, more text on another line, etc, rather than it being one long line.
Continue readingSQLite doesn’t have a concat() function like many other database management systems, such as SQL Server’s concat() and MySQL’s concat().
However, that doesn’t mean you can’t concatenate two strings in SQLite.
SQLite has a concatenation operator (||) that you can use to concatenate two strings.