Can I create computed columns in SQLite?

Question

What would be the syntax (if it's possible), for example, to create a table called Car_Model that has a foreign key to a table Car_Make, and give Car_Make a column which is the number of Car_Models that exist of that Car_Make.

(If this seems trivial or homework-like it's because I am just playing with some python at home trying to recreate a problem I was having at work. We use MS-SQL at work.)

Bosnia

Answer 1

Update: As of version 3.31.0 (released on 2020-01-22), SQLite supports computed columns so the answer below applies to versions prior to 3.31.0

SQLite doesn't supported computed columns.

However your problem can be solved with a relatively simple SQL Query, you can then create a view to make it appear like a table with the extra computed columns.

SELECT Car_Make.Name, Count(*) AS Count 
FROM Car_Make, Car_Model 
WHERE Car_Make.Id = Car_Model.Make 
GROUP BY Car_Make.Name

This should return a table similar to the following

Name      Count
----      -----
Nissan    5
Toyota    20
Ford      10

Halfhardy

Answer 2

Can I create computed columns in SQLite?

Yes, it is possible. This feature was added on 2020-01-22 - SQLite 3.31.0

Generated Columns

Generated columns (also sometimes called "computed columns") are columns of a table whose values are a function of other columns in the same row. Generated columns can be read, but their values can not be directly written. The only way to change the value of a generated columns is to modify the values of the other columns used to calculate the generated column.

CREATE TABLE t1(
   a INTEGER PRIMARY KEY,
   b INT,
   c TEXT,
   d INT GENERATED ALWAYS AS (a*abs(b)) VIRTUAL,
   e TEXT GENERATED ALWAYS AS (substr(c,b,b+1)) STORED
);

Important:

The expression of a generated column may only reference constant literals and columns within the same row, and may only use scalar deterministic functions. The expression may not use subqueries, aggregate functions, window functions, or table-valued functions.

The expression of a generated column may refer to other generated columns in the same row, but no generated column can depend upon itself, either directly or indirectly.

It means that you cannot create a computed column in a way it will depend on other tables as stated in original question. So the viable solutions are view or trigger as already proposed.

Emotive

Parenthesis for computed formula are important, otherway it does not work for me. Also, I suggest to read further about optional keywords "VIRTUAL" and "STORED". It might be important on the application. – Newcomen

@TraxidusWolf The parenthesis for computed formula are already present in the code sample: db<>fiddle. – Emotive

Answer 3

You may apply a trigger to do that. This case, I apply a division between two fields.

CREATE TRIGGER [tUnitPrice] AFTER UPDATE OF [Price], [Qty] ON [tArticles] BEGIN
    UPDATE [tArticles]
    SET    [UnitPrice] = [tArticles].[Price] / [tArticles].[Qty]
    WHERE [rowId] = [NEW].[RowId]; END;

Groth