3

I'm a beginner when it comes to databases and have been reading through the SQLite3 documentation. However, I can't find the answer to the following:

Is it possible to create a SQLite3 database table that has some read-only fields (i.e. not the whole of the table is read-only)?. I know I can stop people writing to the fields via some extra code, but I was wondering if can define a constraint or something similar.

Thanks!

Umbungu
  • 945
  • 3
  • 10
  • 30

4 Answers4

12

You can create a trigger to prevent updates of specific columns:

CREATE TRIGGER ro_columns
BEFORE UPDATE OF col1, col2 ON mytable
BEGIN
    SELECT raise(abort, 'don''t do this!');
END
CL.
  • 173,858
  • 17
  • 217
  • 259
2

Sadly, constraints cannot be added after table creation in SQLite;

It is not possible to rename a column, remove a column, or add or remove constraints from a table.

In other words, no, you cannot make a column read only using a constraint, since creating the table with the (CHECK) constraint would make it impossible to add the read only values to the table in the first place.

Joachim Isaksson
  • 176,943
  • 25
  • 281
  • 294
1

Another way to do this is to rename the table and create a view in its place. You can then use INSTEAD OF triggers to update the table instead.

See http://www.sqlite.org/lang_createtrigger.html

Krumelur
  • 31,081
  • 7
  • 77
  • 119
0

Here is an example table, with a readonly field, that when set to non-zero, the row becomes read-only.

CREATE TABLE test (data TEXT, readonly INTEGER);

Here is the trigger that forbides UPDATE of data when readonly is non-zero:

CREATE TRIGGER test_ro BEFORE UPDATE OF data ON test WHEN OLD.readonly != 0
BEGIN
    SELECT raise(fail, "Read-only data");
END;
matwachich
  • 11
  • 2