Database support for immutable fields

Question

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:

Add a set of triggers to make sure that immutable fields aren't modified
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.

A couple of things could be useful for this: 1. check constraint postgresql.org/docs/9.3/static/ddl-constraints.html & 2. enum data type postgresguide.com/sexy/enums.html — user1509107
– user1509107, Commented Mar 5, 2014 at 14:26

Patrick · Accepted Answer · 2014-03-14 09:35:50Z

3

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.

answered Mar 14, 2014 at 9:35

Patrick

33k7 gold badges73 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Database support for immutable fields

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related