SQL – How to Enforce a Write-Once Then Read-Only Database Table

constraintMySQL

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!

Best Answer

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. This option has no performance overhead, as the privilege check is a part of any statement processing, but it can be overridden by users with elevated privileges, like root.

Another option is to define BEFORE UPDATE and BEFORE DELETE triggers on these tables and use the SIGNAL statement in the trigger body to raise an exception, which would prevent updates and deletes respectively. There is slight performance penalty that you will pay for this, but also some added security, as even privileged users won't be able to get past the error without disabling or dropping the triggers.

Related Solutions

Mysql – How to select data in a sub query only once

If you LEFT JOIN the tables you can then check if the left ordno does not exist. Something like this:

SELECT o.ordno 
FROM orders AS o
LEFT JOIN invoices AS i 
ON i.ordno = o.ordno
WHERE i.ordno IS NULL;

I hope this helps you.

Mysql – Improve performance on write-only table

The thing that slows down INSERTS more than anything is indexes. When you insert data, the indexes must be maintained so they are correct. If you dont have indexes, there will be no overhead in maintaining them.

Some other things you can do if you are still not getting the performance you want is to:

Put the database on a SSD (solid state drive), this will give about a 100-1 performance boost.
If there are multiple tables in your database, get this table onto another drive by itself (Another Database or partition the data if mysql can do that). This allows the engine work on the data for this table while it also works on other requests on the other drive. (This is a trick that I know works in SQL Server, not sure about mysql)

The biggest choke point for databases is the HD. Do what you can to it, and the database will follow.

Best Answer

Related Solutions

Mysql – How to select data in a sub query only once

Mysql – Improve performance on write-only table

Related Question