In SQLite, we can use the strftime() function to return datetime values in our chosen format.
Therefore, we can use it to extract the day, month, and year from a date.
Continue readingCategory: SQLite
How to Drop a Foreign Key in SQLite
Normally if you need to drop a foreign key in SQL, you’d use the ALTER TABLE statement. But if you’re using SQLite, that’s not an option.
SQLite DROP TABLE
In SQLite, you can drop a table with the DROP TABLE statement.
You can optionally add the IF EXISTS clause to suppress any errors that might occur if the table doesn’t exist.
Also, if the table is referenced by a foreign key, there are a few things to be aware of.
Continue readingDealing with Primary Key Conflicts when Inserting Data in SQLite
SQLite has a non-standard SQL extension clause called ON CONFLICT that enables us to specify how to deal with constraint conflicts.
In particular, the clause applies to UNIQUE, NOT NULL, CHECK, and PRIMARY KEY constraints.
This article provides examples of how this clause can be used to determine how to handle primary key constraint conflicts.
By “primary key constraint conflicts”, I mean when you try to insert a duplicate value into a primary key column. By default, when you try to do this, the operation will be aborted and SQLite will return an error.
But you can use the ON CONFLICT clause to change the way SQLite deals with these situations.
One option is to use this clause in the CREATE TABLE statement when creating the table. Doing that will determine how all INSERT operations are treated.
Another option is to use the clause on the INSERT statement whenever you try to insert data into the table. This allows you to take advantage of the clause even when the table wasn’t created with it. When you use this option, the syntax is different; you use OR instead of ON CONFLICT.
The examples on this page use the second option – I create the table without the ON CONFLICT clause, and I instead specify OR on the INSERT statement.
Set a Default Value for a Column in SQLite: DEFAULT Constraint
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.
How iif() Works in SQLite
In SQLite, iif() is a conditional function that returns the second or third argument based on the evaluation of the first argument.
It’s logically equivalent to CASE WHEN X THEN Y ELSE Z END.
iif() is an abbreviation for Immediate IF.
The iif() function was introduced in SQLite 3.32.0, which was released on 22 May 2020.
Add a Generated Column to a Table in SQLite
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 readingHow to Create a Computed Column in SQLite
Generated 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.
Add a Column to an Existing Table in SQLite
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.
What is a Generated Column?
Some database management systems (DBMSs) include a feature called generated columns.
Also known as “computed columns”, generated columns are similar to a normal column, except that a generated column’s value is derived from an expression that computes values from other columns.
In other words, a generated column’s value is computed from other columns.
Continue reading