Chapter 4: CRUD and ACID — Mechanisms for Safely Getting Data In and Out

What you'll learn in this chapter

• Map the four CRUD operations (Create / Read / Update / Delete) to their SQL verbs
• Judge when a transaction is needed and explain the roles of BEGIN, COMMIT, and ROLLBACK
• Explain the four ACID properties (Atomicity / Consistency / Isolation / Durability) in your own words
• Write constraints (NOT NULL / CHECK / foreign keys) in SQL to protect data integrity

Prerequisites

You should understand Chapter 1: The Basic Concept of a Database. No SQL experience is required — even if this is the first time you're seeing the SQL statements in this chapter, the text explains what they're meant to do.

The database used in the SQL examples

Unless noted otherwise, the code examples in this chapter (and the guide as a whole) use the PostgreSQL dialect. They basically work on other RDBMSs like MySQL too, but there are differences in things like auto-numbering (SERIAL) and some constraint syntax. See the note at the end of the chapter for other dialects.

Why center on PostgreSQL

In the Stack Overflow Developer Survey 2025, PostgreSQL is the most-used database, used by 58.2% of professional developers, and its adoption in new projects keeps growing too (see Bytebase's PostgreSQL vs. MySQL 2026). This guide prioritizes the dialect readers are most likely to encounter in modern web development.

The design decisions for running this PostgreSQL as a managed service on AWS (instance sizing, availability, and credential management) are covered in Practical AWS Guide Chapter 9: RDS.

CRUD — The Four Basic Database Operations

Every operation you perform on a database boils down to these four.

Each one maps one-to-one to a SQL verb.

CRUD	SQL verb	Purpose
Create	INSERT	Add new data
Read	SELECT	Search for and retrieve data
Update	UPDATE	Change existing data
Delete	DELETE	Remove data

An example of CRUD operations in an SNS

To make this easier to picture, let's look at when each operation happens using an SNS posting feature as an example.

SQL example

-- Create: add a new post
INSERT INTO posts (user_id, body, created_at)
VALUES (1, 'This is my first post', NOW());

-- Read: the latest 10 posts by user 1
SELECT id, body, created_at
FROM posts
WHERE user_id = 1
ORDER BY created_at DESC
LIMIT 10;

-- Update: edit a post's body
UPDATE posts
SET body = 'I edited this post', updated_at = NOW()
WHERE id = 42;

-- Delete: remove a post
DELETE FROM posts
WHERE id = 42;

Preserving Data Consistency

Consistency refers to a state where the data in a database has no contradictions.

For example, suppose you rename a product in the product master from "Laptop" to "Laptop Pro," but the orders table still has the old name "Laptop" left over. Every time you aggregate a report, you'll run into confusion.

Order ID	Customer	Product Code	Product Name (at order time)	Order Date
1	Taro Yamada	P001	Laptop Pro	2026-01-01
2	Hanako Suzuki	P001	Laptop	2026-01-02
3	Jiro Sato	P001	Laptop Pro	2026-01-03

It's clearly wrong for the same product code, P001, to have different product names.

To prevent this kind of contradiction, you combine two things: a design where the same piece of information lives in exactly one place (normalization), and a mechanism that rejects invalid data from being registered in the first place (constraints).

The detailed steps for normalization are covered in Chapter 11: Normalization. This chapter looks at "constraints."

Validating Data — Constraints

A database has a feature (constraints) for preventing invalid data from being registered. The application also performs its own validation, but it's a safer design to also apply constraints on the database side as a last line of defense.

Commonly used constraints

Example products table definition (PostgreSQL)

CREATE TABLE products (
    id              SERIAL PRIMARY KEY,
    name            VARCHAR(100) NOT NULL,                          -- cannot be blank
    price           NUMERIC(10, 2) NOT NULL CHECK (price > 0),      -- must be greater than 0
    stock_quantity  INTEGER NOT NULL DEFAULT 0 CHECK (stock_quantity >= 0),
    category        VARCHAR(50) NOT NULL CHECK (category IN ('food', 'appliance', 'clothing')),
    email_contact   VARCHAR(255) CHECK (email_contact LIKE '%@%')   -- simple email-format check
);

Constraint	Role
NOT NULL	The column can't be left empty (required)
CHECK (condition)	Guarantees the column's value satisfies a condition
UNIQUE	Guarantees the column's value has no duplicates
PRIMARY KEY	Uniquely identifies a record (NOT NULL + UNIQUE)
FOREIGN KEY	References a record in another table
DEFAULT value	The initial value used when a value is omitted

The email-format CHECK is minimal

In the example above, email_contact LIKE '%@%' is only meant to catch obviously simple typos. Fully RFC-compliant email validation requires an overly complex regular expression, so it's usually done on the application side. Think of the database-side CHECK as a last line of defense against "clearly wrong" values.

Transactions — Guaranteeing "All Succeed, or All Are Rolled Back"

So far we've focused on a single operation. But in real applications, there are frequent situations where you want to treat multiple operations as one unit.

The classic example is a bank transfer. "Subtract 10,000 yen from Account A" and "add 10,000 yen to Account B" are two separate UPDATEs, but if one succeeds and the other fails, money either vanishes or appears out of nowhere.

A transaction is a mechanism that treats multiple operations together as a single "unit."

• BEGIN (or START TRANSACTION): starts a transaction
• COMMIT: confirms (persists) everything done so far
• ROLLBACK: discards everything done so far and reverts to the state before it started

SQL example

A bank-transfer transaction (PostgreSQL)

BEGIN;

UPDATE accounts SET balance = balance - 10000 WHERE id = 'A';
UPDATE accounts SET balance = balance + 10000 WHERE id = 'B';

-- e.g., check that the balance hasn't gone negative

COMMIT;  -- confirm if both succeeded
-- if there was an error, ROLLBACK; instead

ACID Properties — The Four Promises a Transaction Keeps

For a transaction to function as a mechanism you can trust, a database guarantees the following four properties. Taking the first letter of each, they're called the ACID properties.

Property	Meaning	Concrete example in a bank transfer
Atomicity	Every operation in a transaction either all succeeds or all fails	If the server crashes right after debiting A, A is restored too if B was never credited
Consistency	The transaction ends in a state that satisfies its constraints	If there's a "balance must be at least 0" constraint, a transfer that would violate it is rejected
Isolation	A concurrent transaction can't see an in-progress, half-finished state	If someone else reads the balance mid-transfer, they never see the half-done state right after the debit
Durability	A COMMITted change is never lost, even in a failure	If the server crashes right after the transfer completes, the transfer history and balance are preserved

Why does "consistency" show up twice?

The word "consistency" is used in two senses: a state where data has no contradictions (as described earlier in this chapter), and the "C" in ACID (ending in a state that satisfies its constraints). In practice these mean almost the same thing — they're just two different angles on describing "the database never ends up in a broken state."

Disaster Recovery and Backups

A database also has mechanisms for protecting data from failures such as hardware breakdowns, software bugs, human error, and disasters. This guide introduces only the concepts; full operational practice (designing a backup policy, running replication, etc.) is out of scope.

Mechanism	Roughly what it does
Backup	Copies the entire dataset and stores it somewhere else
Replication	Copies changes to another server in real time
Transaction log	Records confirmed operations in order, so you can roll back to a specific point
Redundancy	Runs multiple servers with the same role so service continues even if one fails

Out of scope for this guide

Full backup operations (combining full / differential / incremental backups, the 3-2-1 rule, PITR, etc.) and replication topologies (synchronous / asynchronous, leader-follower) are operational-design topics, so they're out of scope for this guide. If you use a managed database from a cloud provider (AWS RDS, Google Cloud SQL, etc.), most of this is handled for you automatically.

Exercises

Exercise 1: ACID quiz

Exercise 1-1

What does the D in ACID stand for? And what does it guarantee?

Sample answer

D = Durability. It guarantees that once a change is confirmed with COMMIT, it isn't lost even if a failure (server outage, power loss, etc.) occurs afterward.

Mechanically, it's common for the DBMS to write the change to a safe area on disk (the transaction log) at COMMIT time before responding that it's confirmed. That way, even if the in-memory data is lost, it can be recovered from the log.

A common mistake: confusing "Durability" with "Distributed." Distributed isn't part of ACID — it belongs to a different topic (like the CAP theorem).

Exercise 1-2

Which of the following situations need a transaction? Select all that apply.

1. Fetching and displaying a single article on a blog site
2. Confirming an order on an e-commerce site, decrementing stock, and recording payment information
3. Pressing an SNS "like" button, which records both the post's "like count" and who liked it
4. Fetching keyword search results on a search page

Sample answer

2 and 3 apply.

• 2 (confirming an order): you want "create the order, decrement stock, and record payment information" to all succeed together. If any step fails partway through, you want everything rolled back (for example, if payment fails but stock was already decremented, you lose a sale).
• 3 (likes): you want "increment the like count by 1" and "add a record of who liked it" to happen together. If only one succeeds, the count and the list become inconsistent.
• 1 (fetching an article): it's read-only, so a transaction's "all succeed or all rolled back" meaning barely applies.
• 4 (search): also read-only.

Additional note: even "read-only" cases like 1 and 4 sometimes use a transaction if you want multiple queries to see the same snapshot (for example, so an aggregate stays consistent even if someone else updates the data mid-aggregation). That's a topic about isolation, which this guide doesn't go into further.

Exercise 2: Add constraints

Exercise 2

Add three constraints to the following users table definition.

CREATE TABLE users (
    id         SERIAL PRIMARY KEY,
    email      VARCHAR(255),
    age        INTEGER,
    role       VARCHAR(20)
);

Requirements:

• The email address must be required and unique
• Age must be between 0 and 150
• The role must be one of 'admin' / 'editor' / 'viewer', defaulting to 'viewer'

Sample answer

users table (with constraints added)

CREATE TABLE users (
    id     SERIAL PRIMARY KEY,
    email  VARCHAR(255) NOT NULL UNIQUE,
    age    INTEGER CHECK (age >= 0 AND age <= 150),
    role   VARCHAR(20) NOT NULL DEFAULT 'viewer'
           CHECK (role IN ('admin', 'editor', 'viewer'))
);

Why this works:

• NOT NULL UNIQUE: combines "required" (NOT NULL) with "no duplicates" (UNIQUE)
• CHECK (age >= 0 AND age <= 150): bounds the range with two conditions
• CHECK (role IN ('admin', 'editor', 'viewer')): only allows the listed values
• DEFAULT 'viewer': the initial value used when a value is omitted

Alternative approach: in PostgreSQL, you could also define a separate enumerated type, ENUM, and use that.

CREATE TYPE user_role AS ENUM ('admin', 'editor', 'viewer');
CREATE TABLE users (
    -- ...
    role   user_role NOT NULL DEFAULT 'viewer'
);

An ENUM is less flexible — adding a value later requires ALTER TYPE — but the list of roles is explicit as a type, which makes it easier to maintain. Which one to use comes down to team preference.

A common mistake: writing email VARCHAR(255) NOT NULL and forgetting UNIQUE. That lets multiple accounts be created with the same email address, which causes trouble during login.

Note: Syntax in Other RDBMSs

This chapter used the PostgreSQL dialect, but other major RDBMSs differ in some syntax.

Syntax	PostgreSQL	MySQL	SQL Server
Auto-incrementing primary key	SERIAL or GENERATED ALWAYS AS IDENTITY	AUTO_INCREMENT	IDENTITY(1,1)
Starting a transaction	BEGIN or START TRANSACTION	START TRANSACTION or BEGIN	BEGIN TRANSACTION
Current date-time	NOW() or CURRENT_TIMESTAMP	NOW()	GETDATE()
String concatenation	|| or CONCAT()	CONCAT() (|| is the OR operator by default; it becomes concatenation in PIPES_AS_CONCAT mode)	+ or CONCAT()

The CRUD basics (SELECT / INSERT / UPDATE / DELETE) and constraints (NOT NULL / CHECK / UNIQUE / FOREIGN KEY) work almost identically across every RDBMS.

Summary

Here's a recap of what this chapter covered.

• CRUD maps to the four verbs INSERT / SELECT / UPDATE / DELETE
• Constraints (NOT NULL / CHECK / UNIQUE / FOREIGN KEY) can reject invalid data from being registered in the first place
• A transaction treats multiple operations as one unit of "all succeed, or all are rolled back"
• The four ACID properties (atomicity, consistency, isolation, durability) underpin a transaction's reliability

The next chapter looks at why we handle data in the form of tables — the thinking behind relational databases.

Next chapter

Chapter 5: Introduction to Relational Databases