Transactional Integrity

What is 'transactional integrity' and why does it matter?

Transactional Integrity is a property that relational databases are built to enforce any time there is a change to your database, and it's a crucially important advantage of using a database rather than some other kind of information organization and storage.

Note

Much of this material comes from this helpful discussion on the blogging platform Medium.

Transactional Integrity and human errors

Imagine you are using a spreadsheet to keep track of your information, and you're sharing that spreadsheet with other people on your team who are all trying to help add and maintain the information in it.

If you're working on a document sharing site like Google Docs you'll be familiar with features in those systems that allow you to, for example, see other people's cursors in real time - this ensures that you don't accidentally mess up each others' changes, and the document only accepts each person's change either completely or not at all.

That's kind of the same thing as ensuring transactional integrity (and behind the scenes, those systems do use databases that enforce transactional integrity for this purpose!).

But imagine a world in which your spreadsheet got confused and accepted half of your change and half of your colleague's change to a value, then saved that mishmash. You could end up with garbage rather than correct information. This is the kind of problem transactional integrity ensures you do not have to deal with.

Other kinds of errors that you can often make in a spreadsheet but that a properly designed database will prevent are things like this:

• you enter text into a column that should only contain numbers
• you leave a cell blank that should not be blank
• you enter a primary key that is not unique
• you enter a foreign key that doesn't actually exist in the external table (we have a page about database keys if you'd like a review)

these kinds of mistakes are prevented by the aspect of transactional integrity that enforces constraints you set up on your tables when you designed them. Because database tables are so carefully designed, they can save us from ourselves in many ways!

Transactional Integrity and computer errors

Have you ever experienced a problem where your computer suddenly crashes, or the power goes out and you're in mid-update? A database that enforces transactional integrity protects you from computer errors like these, and it always ensures that the changes you've made to your database are either complete and correct or else completely rejected.

/// idea

Sometimes it seems like the completely rejected part is unduly harsh; but it's actually super helpful. Honest.

///

The ACID test of transactional integrity

A database that enforces transactional integrity uses the following principles to protect you from human and computer errors:

Atomicity: every change to your database is treated as one whole thing that either succeeds entirely, or else fails entirely. This ensures that your database will not be messed up because a change left things in a partial or incomplete state.

Consistency: every change to your database is done in a way that's consistent with the rules of the database. For example, if a column is set up as an 'id' and an 'integer', but you try to enter a character other than an integer in that field (who here has not accidentally typed the letter 'o' instead of the number zero, or the letter 'l' instead of the number one?), the change will be rejected. This ensures that your database will continue to work after the change is made.

Isolation: every change is separate from every other change. One advantage of using a database is that you can have many people adding or editing data at the same time. If one person submits a change that is correct, but another person submits a change that has an error, you want the correct one to go through, and the incorrect one to be rejected - you don't want the two requests to get mixed up so that both are rejected, right? This ensures that one bad transaction doesn't mess up other good transactions.

Durability: once a change is made and accepted, it persists. If you make a change to your database, then shut the database down, then come back the next day and open it up again, you want that change to still be there, right? This ensures that you won't lose data that you've added to your database.

Summary

Principles of transactional integrity are fundamental to the utility of databases over other kinds of data organization and storage. If you're working with small amounts of information, you might not see the ACID principles as super important; but the more information you're in charge of - and the more people use that information - the more important it is!

How'd we do?

Do you feel like you understand primary and foreign keys? If so, great! If not, we hope you'll ask more questions.

You can contact John, Gus, or Amy and we'll do the best we can to help!

At this point, you might want to go back to our Day 2 page 🚀 or to the CoLang Workshop Index 🚀