Why not constant columns ?

(1) By anonymous on 2020-12-11 17:34:51 [source]

SQLite has columns for unique, non-null values. But I still feel a need for a 'constant' column, i.e a column whose value can only be 'INSERT'ed and not 'UPDATE'd. What do you think?

(2) By Keith Medcalf (kmedcalf) on 2020-12-11 22:04:08 in reply to 1 [link] [source]

You can prevent columns from being updated by using a trigger:

create trigger triggerName before update of columnName on tableName
begin
  select raise(ABORT, 'Updating tableName.columnName is prohibited');
end;

which will prevent you from using tableName.columnName as the target of an update statement.

(3) By Keith Medcalf (kmedcalf) on 2020-12-11 23:00:45 in reply to 1 [link] [source]

Specific example:

SQLite version 3.35.0 2020-12-11 14:46:25
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table x(x);
sqlite> create trigger bu_x_x before update of x on x
   ...> begin
   ...>   select raise(ABORT, 'Cannot update column x.x');
   ...> end;
sqlite> insert into x values (1);
sqlite> update x set x = 4;
Error: Cannot update column x.x
sqlite> select * from x;
┌───┐
│ x │
├───┤
│ 1 │
└───┘
sqlite>