CODEWITHSUNDEEP
databasesqliteconstraints
Umbungu
Umbungu

Reputation: 975

Read-only fields in SQLite3 database table?

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!

Upvotes: 4

Views: 5961

Answers (4)

matwachich
matwachich

Reputation: 11

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;

Upvotes: 0

Joachim Isaksson
Joachim Isaksson

Reputation: 181027

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.

Upvotes: 2

Krumelur
Krumelur

Reputation: 32577

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

Upvotes: 1

CL.
CL.

Reputation: 180192

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

Upvotes: 13

Related Questions