CODEWITHSUNDEEP
sqlpostgresqlimmutability
Andrew Barrett
Andrew Barrett

Reputation: 19901

Database support for immutable fields

We're about to start a new project and I'm breaking down the models at the moment. Most of the entities I'm modelling are meant to be immutable.

While I can control this to a certain extent at the code level by using something like django-immutablemodel, I'd be more comfortable if I could enforce this at the database level as well.

I'm planning to use postgresql although would be willing to consider alternatives if they supported this. From what I can tell the two main ways to do this currently are:

  1. Add a set of triggers to make sure that immutable fields aren't modified
  2. Enforce the immutability through user rights (i.e. don't give user update rights to columns that you want immutable)

I'd be interested if anyone has tried these methods and can comment on them or knows a better way to do this.

For some fields I want to be an effective write-once, so if the field is NULL allow it to be updated to a value, but never allow a field with a value to be updated. That would suggest I need to go down the trigger route.

Upvotes: 3

Views: 3461

Answers (1)

Patrick
Patrick

Reputation: 32244

If most of the entities (tables? you mention columns later on) are immutable then place that information in separate tables and revoke all access privileges to these tables. Create a second table for modifiable data, again with all privileges revoked, and link the two with a key. Now create a view that is built from both tables and create an INSTEAD OF INSERT OR UPDATE OR DELETE trigger which restricts updates to the modifiable table.

There are other solutions possible, such as with column privileges, but the above solution has the nice side effect that you can optimize read-only table access and that you only have to back up the few tables that are modifiable.

Upvotes: 3

Related Questions