27

Is it even possible?

My use case is a ledger table, with a requirement that once a record is created, it should be read-only, i.e. no-one should be able to edit or delete it. This only applies to the ledger table and tables with a direct relation to it - there are other tables in the same schema which will be updated/deleted as normal.

My understanding is that for data integrity purposes these sorts of constraints should be applied at the database layer, but I can't find a clean, widely accepted way of doing this - is this a use case where I'd just be better doing it in the application layer?

The ideal would be for a way to do it in plain SQL, so as to be agnostic of what DB platform is used, since that may be subject to change, but I realise that may be too much to ask for, so if it has to be platform-dependent, some flavour of MySQL is preferred.

Thank you!

43

I see at least two ways of accomplishing this. The first approach is to not grant DELETE and UPDATE privileges on these write-once tables, or, for that matter, any privileges apart from INSERT and SELECT, thus only allowing users to insert into or select from them.

Another option is to define BEFORE UPDATE and BEFORE DELETE triggers on these tables and use the SIGNAL statement to raise an exception in the trigger body, which would prevent updates and deletes respectively.

  • 6
    I would recommend both options since you make your intention clear and make a do have to take multiple deliberate actions to violate it – Adam Martin Nov 21 '17 at 22:50
  • 3
    Triggers are a better choice since they fire on all transactions, including those undertaken by administrative users, and can give a more-specific error message. – Blrfl Nov 22 '17 at 12:11
10

Permissions seem to to be the obvious choice - however you could also use the ARCHIVE Storage Engine. This table engine is designed to record large amounts of data that will not change:

The ARCHIVE engine supports INSERT, REPLACE, and SELECT, but not DELETE or UPDATE. It does support ORDER BY operations, BLOB columns, and basically all but spatial data types (see Section 11.5.1, “Spatial Data Types”). The ARCHIVE engine uses row-level locking.

The difference to permissions is that someone with extended privileges would still be able to change data on most other table types, while ARCHIVE does not allow anyone to alter data that is already in the table.

  • 1
    From here, it appears that REPLACE is a sort of UPDATE! "REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted. See Section 13.2.5, “INSERT Syntax”." – Vérace Nov 22 '17 at 6:42
7

Look into "Point in Time Architecture" or "Temporal Database Architecture"

Database Design: A Point in Time Architecture

In most relational database implementations. Update and Delete commands destroy the data that was there prior to their issue. However, some systems require that no information is ever physically deleted from or updated in the database. In this article, Arthur Fuller presents a solution to this requirement in the form of a Point-in-Time architecture: a database design which allows a user to recreate an image of the database as it existed at any previous point in time, without destroying the current image.

Temporal database

From Wikipedia, the free encyclopedia
Temporal database stores data relating to time instances. It offers temporal data types and stores information relating to past, present and future time.

The basic ideas of both is that you either need to add data without deleting - or store data in such a way that you can pull the data as it exists currently... or existed at a previous datetime.

related question here: how-to-create-a-point-in-time-architecture-in-mysql,

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.