Back in More Database Creation Topics I gave an example of a transaction that moved money from one bank account to another. As the example illustrated, a transaction can prevent modification A from being made to the database unless modification B is also made. In other words, in a transaction, either all the modifications are made to the database (commit) or none of the modifications are made (roll back). Let’s take our definition of a transaction and expand on it.
The one line definition of a transaction is “a sequence of operations performed as a single logical unit of work.” To be considered a “transaction,” a single logical unit of work must exhibit the ACID (Atomicity, Consistency, Isolation, and Durability) properties:
Atomicity – A transaction must be an atomic unit of work where either all of its modifications are made, or none of them are made.
Consistency – When finished a transaction must leave all data in a consistent state. In this case “consistent state” means that all rules, or constraints, are applied to a transaction’s modifications.
Isolation – Changes made by simultaneous transactions must be isolated from the changes made by any and all other simultaneous transactions. In other words a transaction will either “see” the data in the state it was in before another simultaneous transaction modified it, or it will see the data after the second transaction has completed, but it will not see an intermediate state. This is known as serializability because it is possible to load the data back to a starting point, “replay” the transactions, and end up with data in the same state as it was after the original transactions were made.
Durability – Once a transaction is committed, the data modifications are permanent and can withstand system failure.
The good news is that from a physical/technical standpoint SQL Server does all of the work of implementing the ACID properties. However, the logic behind what is considered a transaction and if a modification is or is not valid is the job of the database administrator and/or programmer.
Because choosing when a transaction begins and ends is more of a SQL Server programming issue rather than an administration issue, it will not be covered. Instead, the rest of this article is going to focus on the latter, how to implement data validation.
There are many types of constraints that we can use to control the validation of data, including some that we have already used such as Allow Nulls and Data Types. There are however many more that we will look at over the next few articles. The rest of this article will focus on setting up foreign key constraints with check constraints following in my next article.