SQL for Database

Chapter 8
Data Modification and Integrity

Bachelors Program in Full Stack Development
SQL for Database   •   Bachelor

Data Modification and Integrity

Introduction

A database is useful only when its stored data remains accurate, consistent, and meaningful. Data modification commands—INSERT, UPDATE, DELETE, and MERGE—allow applications and administrators to change database state. Those changes must be controlled by integrity rules that prevent invalid entities, broken relationships, and unacceptable values.

This chapter examines safe data modification in relational databases. It explains entity, referential, and domain integrity; generated values and defaults; constraint violations; transactions; concurrency concerns; and professional practices for reliable application development. SQL syntax varies slightly among database products, so examples use widely supported SQL concepts and identify important dialect differences.

Learning Objectives

By the end of this chapter, you should be able to:

  • perform INSERT, UPDATE, DELETE, and MERGE operations safely;
  • distinguish entity, referential, and domain integrity;
  • define and use primary keys, foreign keys, unique constraints, checks, defaults, and generated values;
  • predict the effect of NULL, cascading actions, and constraint violations;
  • use transactions, validation, and row-count checks to protect modifications;
  • explain concurrency, idempotency, auditing, and governance considerations;
  • analyze a realistic data-modification problem and design an appropriate solution.

1. Database State and Data Modification

A relational database contains relations (tables) whose rows represent entities or associations. A modification changes the database state. The desired result is not merely syntactically valid SQL; it is a state that satisfies all declared integrity constraints.

For example, an order-management database may contain Customer, Product, and OrderLine tables. An order line must refer to an existing product, its quantity must be positive, and its order identifier must identify an existing order. A command that violates any of these conditions should be rejected or handled according to a deliberately chosen policy.

A reliable modification has four properties:

  1. Correct target — it changes only intended rows.
  2. Valid values — each value satisfies its domain and constraints.
  3. Atomicity — related changes succeed together or are rolled back together.
  4. Observability — the application can determine what happened and record important events.

2. INSERT: Adding Rows

The basic form of an insert is:

INSERT INTO Customer (customer_id, full_name, email)
VALUES (101, 'Mina Patel', '[email protected]');

Listing the target columns is strongly recommended. It prevents errors when the table definition changes and makes the statement’s intention explicit. Omitting the column list relies on physical column order and requires a value for every non-generated column.

Multiple rows can be inserted in one statement:

INSERT INTO Product (product_id, product_name, unit_price)
VALUES
    (10, 'Keyboard', 45.00),
    (11, 'Mouse', 18.50),
    (12, 'Monitor', 220.00);

A table can also be populated from a query:

INSERT INTO CustomerArchive (customer_id, full_name, email)
SELECT customer_id, full_name, email
FROM Customer
WHERE status = 'INACTIVE';

The selected columns must be compatible with the target columns, and duplicate or conflicting values may cause a constraint violation.

2.1 Generated Values and Defaults

Many tables generate identifiers automatically. Common mechanisms include identity columns, sequences, and generated UUIDs. Product-specific syntax differs. For example, a database may define an identity column as follows:

CREATE TABLE Customer (
    customer_id INTEGER GENERATED ALWAYS AS IDENTITY,
    full_name   VARCHAR(100) NOT NULL,
    email       VARCHAR(255) NOT NULL UNIQUE
);

When the database generates the identifier, the insert normally omits that column:

INSERT INTO Customer (full_name, email)
VALUES ('Mina Patel', '[email protected]');

A default supplies a value when the column is omitted:

CREATE TABLE Account (
    account_id  INTEGER PRIMARY KEY,
    status      VARCHAR(20) NOT NULL DEFAULT 'ACTIVE',
    created_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

A crucial distinction is that omitting a column may invoke its default, whereas explicitly inserting NULL generally does not invoke the default. If the column is NOT NULL, explicit NULL is rejected.

Generated values are not necessarily gap-free. A sequence or identity generator may consume a number even if a transaction later rolls back. Applications should treat generated identifiers as unique keys, not as evidence of an uninterrupted count.

3. UPDATE: Changing Existing Rows

The general form is:

UPDATE Customer
SET email = '[email protected]'
WHERE customer_id = 101;

The WHERE clause is essential. Without it, every row is updated:

UPDATE Customer
SET status = 'INACTIVE';

That statement may be correct for a deliberately global operation, but it is dangerous when accidental. A safe workflow previews the target first:

SELECT customer_id, status
FROM Customer
WHERE last_login < DATE '2023-01-01';

Then the update is executed and its affected-row count is checked. In application code, optimistic concurrency can prevent overwriting another user’s change:

UPDATE Product
SET unit_price = 49.00,
    version_no = version_no + 1
WHERE product_id = 10
  AND version_no = 3;

If the affected-row count is zero, the row may have been changed by another transaction or may not exist. The application should detect this rather than silently reporting success.

An update can affect several columns, but each new value must satisfy all relevant constraints. For example, setting a child row’s foreign key to an absent parent identifier is rejected unless the column permits NULL and NULL is used instead.

4. DELETE: Removing Rows

A delete removes rows matching its predicate:

DELETE FROM Customer
WHERE customer_id = 101;

As with UPDATE, omitting WHERE deletes all rows. Deleting a parent row may affect child rows through the foreign key action selected at table creation:

  • RESTRICT or NO ACTION: reject the parent deletion while dependent rows exist;
  • CASCADE: delete dependent rows automatically;
  • SET NULL: replace the child foreign key with NULL, if permitted;
  • SET DEFAULT: replace it with the column’s default, if supported and valid.

Cascades are useful for genuinely dependent data, such as order-line records belonging exclusively to an order. They are risky when a parent has many descendants or when deletion would destroy legally or operationally important history. Soft deletion—retaining a row and setting deleted_at or is_active—may be more appropriate, although it requires every relevant query to apply the correct filter.

A safe deletion process identifies dependencies, previews affected rows, starts a transaction, performs the deletion, checks the count, and commits only after verification.

5. MERGE and Upsert Processing

MERGE combines matching and nonmatching logic. It is useful when synchronizing a source dataset with a target table:

MERGE INTO Product AS target
USING ProductImport AS source
ON target.sku = source.sku
WHEN MATCHED THEN
    UPDATE SET
        product_name = source.product_name,
        unit_price = source.unit_price
WHEN NOT MATCHED THEN
    INSERT (sku, product_name, unit_price)
    VALUES (source.sku, source.product_name, source.unit_price);

The exact syntax and behavior differ among database systems. Some products provide an INSERT ... ON CONFLICT, INSERT ... ON DUPLICATE KEY UPDATE, or equivalent “upsert” statement instead.

A merge must be designed carefully:

  • the matching key should be stable and unique;
  • the source should not contain multiple rows matching one target row unless the product and design explicitly support that situation;
  • source data should be validated before modification;
  • the operation should report inserted, updated, and rejected rows;
  • unintended deletion clauses should be avoided unless fully tested.

A merge is not automatically safe merely because it is one statement. It can still overwrite trusted values with stale source values or create incorrect matches.

6. Integrity Constraints

Integrity constraints express rules that the DBMS enforces. They are preferable to relying exclusively on application code because all clients—web applications, scripts, services, and administrators—are subject to the same database rules.

6.1 Entity Integrity

Entity integrity requires every row to be identifiable. A primary key must be unique and cannot be NULL.

CREATE TABLE Department (
    department_id INTEGER PRIMARY KEY,
    department_name VARCHAR(80) NOT NULL UNIQUE
);

A composite primary key uses multiple columns:

CREATE TABLE Enrollment (
    student_id INTEGER,
    course_id INTEGER,
    enrolled_on DATE NOT NULL,
    PRIMARY KEY (student_id, course_id)
);

The combination must be unique even though each individual value may appear repeatedly. A candidate key is any minimal column set that uniquely identifies rows; one candidate key is selected as the primary key, while other candidate keys may receive UNIQUE constraints.

6.2 Referential Integrity

Referential integrity requires a foreign-key value to match an existing candidate or primary key in the parent table, unless the foreign-key column is nullable and the value is NULL.

CREATE TABLE Employee (
    employee_id INTEGER PRIMARY KEY,
    department_id INTEGER NOT NULL,
    CONSTRAINT fk_employee_department
        FOREIGN KEY (department_id)
        REFERENCES Department(department_id)
);

This prevents an employee from referencing a nonexistent department. The parent usually must be inserted before the child, and a parent generally cannot be deleted while referenced children remain unless a cascading or other action has been defined.

6.3 Domain Integrity

Domain integrity restricts values to an appropriate set or type. It includes data types, NOT NULL, CHECK, UNIQUE, and defaults.

CREATE TABLE Product (
    product_id INTEGER PRIMARY KEY,
    sku VARCHAR(30) NOT NULL UNIQUE,
    unit_price DECIMAL(10,2) NOT NULL CHECK (unit_price >= 0),
    stock_quantity INTEGER NOT NULL CHECK (stock_quantity >= 0),
    status VARCHAR(15) NOT NULL DEFAULT 'ACTIVE'
        CHECK (status IN ('ACTIVE', 'DISCONTINUED'))
);

A data type prevents many invalid representations, but it may not express business rules. The CHECK constraints above prevent negative prices and stock quantities. More complex cross-row or cross-table rules may require triggers, assertions where supported, procedures, or carefully designed application transactions.

6.4 Constraint Timing and Naming

Constraints may be immediate, meaning they are checked during each statement, or deferrable, meaning checking can be postponed until transaction commit in systems that support deferrable constraints. Deferral can help with circular dependencies, but it increases complexity.

Named constraints improve diagnostics and maintenance. A message such as fk_employee_department is more useful than an unnamed system-generated identifier.

7. NULL and Three-Valued Logic

NULL means unknown, missing, or not applicable; it does not mean zero or an empty string. Comparisons with NULL produce an unknown result rather than TRUE or FALSE.

Incorrect:

WHERE manager_id = NULL

Correct:

WHERE manager_id IS NULL

SQL predicates operate with three logical values: true, false, and unknown. A WHERE clause returns only rows for which the predicate is true. This affects updates and deletes. For example, an attempted cleanup using WHERE discount <> 0 will not select rows where discount is NULL.

Use COALESCE when a deliberate replacement is appropriate, but do not use it to conceal missing information without understanding the business meaning.

8. Transactions and Safe Modification

A transaction groups statements into one logical unit. The ACID properties are:

  • Atomicity: all changes commit, or none commit;
  • Consistency: constraints and transaction rules remain satisfied;
  • Isolation: concurrent transactions do not produce unacceptable interference;
  • Durability: committed changes survive an appropriate system failure.

Example:

BEGIN;

UPDATE Account
SET balance = balance - 100.00
WHERE account_id = 1
  AND balance >= 100.00;

-- Application checks that exactly one row was updated.

UPDATE Account
SET balance = balance + 100.00
WHERE account_id = 2;

COMMIT;

If the first update affects zero rows, the application should issue ROLLBACK rather than crediting the second account. In a banking system, the two updates belong in one transaction because a transfer is not valid if only one side occurs.

Isolation levels influence visibility and locking. Higher isolation can reduce anomalies but may reduce concurrency. Common anomalies include dirty reads, non-repeatable reads, and phantom rows. The appropriate level depends on the operation, DBMS, workload, and performance requirements.

9. Constraint Violations and Error Handling

A modification can fail because of:

  • duplicate primary or unique key;
  • missing required value;
  • invalid check condition;
  • nonexistent foreign-key parent;
  • attempted parent deletion with dependent children;
  • type, length, precision, or conversion error;
  • deadlock or serialization conflict.

Applications should treat database errors as meaningful outcomes. They should roll back the affected transaction, log sufficient diagnostic information without exposing confidential data, and present a useful response to the user. Retrying is appropriate for transient deadlocks or serialization failures when the operation is safe to repeat; it is not an appropriate response to a permanent validation error.

Error handling should avoid partial success. Batch loading may use a staging table: load and validate raw data first, report rejected rows, then insert only approved rows into production tables.

10. A Practical Safety Framework

Before modifying production data, apply the following sequence:

  1. Define the business operation. State exactly which rows and values should change.
  2. Inspect the schema. Identify keys, foreign keys, defaults, checks, triggers, and cascades.
  3. Preview the predicate. Run the equivalent SELECT and inspect representative results.
  4. Use parameters. Parameterized statements reduce injection risk and improve type handling.
  5. Start a transaction. Keep related modifications together.
  6. Validate affected-row counts. An unexpected count is a warning, not a minor detail.
  7. Commit deliberately. Roll back when a prerequisite fails.
  8. Audit important changes. Record actor, time, operation, and relevant before-and-after information.
  9. Test backups and recovery. Integrity includes the ability to recover from operator error.

Never construct SQL by concatenating untrusted user input. For example, a login or search value should be passed as a parameter rather than inserted into a SQL string.

11. Case Study: Inventory Import for a Retail System

The following is an illustrative case.

Background

A retailer receives a daily file containing SKU, product name, price, and stock quantity. The production Product table has a unique SKU, nonnegative price and stock checks, and a generated product identifier.

Problem

An early import script directly updated production rows and inserted new rows. It sometimes contained duplicate SKUs, negative stock values, and blank prices. In addition, a failed network connection left some products updated and others untouched.

Analysis

The process lacked staging, transaction boundaries, source validation, and clear duplicate handling. Directly importing the file also risked overwriting trusted prices with stale data.

Approach and Solution

The redesigned process was:

  1. load the file into ProductImport, preserving the source row number;
  2. validate required fields, data types, duplicate SKUs, and nonnegative numeric values;
  3. reject invalid rows with explicit error messages;
  4. compare valid rows with production using SKU as the stable match key;
  5. execute a controlled MERGE or product-specific upsert inside a transaction;
  6. update only approved attributes and record the source batch identifier;
  7. verify inserted and updated counts before committing;
  8. retain the import file and audit results for reconciliation.

A simplified validation query might be:

SELECT source_row, sku, stock_quantity
FROM ProductImport
WHERE sku IS NULL
   OR stock_quantity < 0;

The import service also used an idempotent batch identifier. Reprocessing the same accepted file did not create duplicate products or repeatedly apply an unintended adjustment.

Outcome

Invalid records became visible rather than silently corrupting production data. Partial imports were eliminated, reconciliation became possible, and operators could distinguish rejected, inserted, updated, and unchanged records.

Learning Points

Integrity is shared between schema design and operational process. Constraints provide the final protection, while staging, validation, transactions, and auditing make the process understandable and recoverable.

12. Limitations, Governance, and Contemporary Practice

Constraints cannot express every business policy. A rule such as “a customer may not place more than five pending orders per day” involves multiple rows and time. It may require a transactionally safe procedure, indexed queries, or a separate domain service. Triggers can enforce complex rules close to the data, but they may surprise application developers and complicate debugging.

Data modification also has governance implications. Personal data should be modified only by authorized users, and access should follow least privilege. Audit records should be protected from unauthorized alteration. Retention and deletion policies must respect contractual, legal, and regulatory requirements; a soft delete is not automatically equivalent to legally required erasure.

Modern systems increasingly use migration tools, schema version control, change-data-capture pipelines, and automated validation in continuous integration. AI-assisted coding can suggest SQL, but generated statements must be reviewed for missing predicates, unsafe cascades, privilege misuse, and dialect-specific behavior. A human remains responsible for testing and approving changes.

Best Practices Checklist

  • Declare primary keys and appropriate candidate-key constraints.
  • Use foreign keys rather than relying only on application assumptions.
  • Use NOT NULL, types, defaults, and CHECK constraints for domain rules.
  • Name constraints clearly.
  • List columns explicitly in INSERT statements.
  • Preview UPDATE and DELETE predicates with SELECT.
  • Use transactions for related changes.
  • Check affected-row counts and handle zero or unexpected counts.
  • Parameterize all externally supplied values.
  • Stage and validate bulk imports.
  • Design merge keys carefully and make repeat processing safe.
  • Audit sensitive or consequential modifications.
  • Test backups, rollback procedures, and recovery.

Chapter Summary

Data modification commands change relational database state and must be used with precision. INSERT adds rows, UPDATE changes selected rows, DELETE removes rows, and MERGE synchronizes matched and unmatched data. Generated values and defaults reduce manual key management, but omitted columns and explicit NULL have different effects.

Entity integrity ensures that rows have valid unique identifiers. Referential integrity ensures that foreign keys refer to valid parent rows. Domain integrity uses types, NOT NULL, UNIQUE, CHECK, and defaults to restrict values. Transactions provide atomicity and support reliable multi-step operations. Safe practice requires parameterization, predicate preview, row-count verification, careful error handling, staging for imports, auditing, and appropriate concurrency control.

Key Terms

Primary key: Column or column set that uniquely identifies each row and cannot be null.

Candidate key: Minimal column set capable of uniquely identifying a row.

Foreign key: Column or column set referencing a key in another table.

Entity integrity: Requirement that every row has a valid, non-null identity.

Referential integrity: Requirement that foreign-key references remain valid.

Domain integrity: Requirement that values conform to defined types and rules.

INSERT: SQL statement that adds rows.

UPDATE: SQL statement that changes existing rows.

DELETE: SQL statement that removes rows.

MERGE: Statement that applies different actions to matched and unmatched source rows.

Default: Value supplied when an insert omits a column.

Generated value: Value produced automatically by the database.

NULL: Marker for unknown, missing, or inapplicable information.

Constraint: Database rule that restricts permitted states.

Cascade: Automatic action affecting dependent rows after a parent modification.

Transaction: Logical unit of work committed or rolled back as a whole.

Idempotency: Property that safely repeating an operation produces the intended same result.

Staging table: Temporary or intermediate table used to validate incoming data.

Optimistic concurrency: Strategy that detects conflicting changes, often with a version value.

Soft deletion: Retaining a row while marking it inactive or deleted.

Review Questions

Short Answer Questions

  1. Why should an UPDATE statement normally contain a WHERE clause?
  2. Distinguish a default value from an identity-generated value.
  3. What does a foreign key protect?
  4. Why is NULL not tested with the equality operator?
  5. State the four ACID properties of a transaction.

Long Answer Questions

  1. Explain entity, referential, and domain integrity, giving one SQL example of each.
  2. Compare RESTRICT, CASCADE, SET NULL, and soft deletion as approaches to parent-row removal.
  3. Describe a safe workflow for executing a high-risk bulk UPDATE.
  4. Explain the principal risks and controls associated with MERGE operations.
  5. Discuss how staging tables and transactions improve the reliability of bulk imports.

Analytical / Application Questions

  1. A transfer subtracts money from one account but fails before crediting another. Design a transaction and error-handling strategy that prevents this inconsistent result.
  2. An import contains two rows with the same SKU and one negative quantity. Propose validation rules, database constraints, and an operational response.
  3. A customer deletion is blocked by orders that must be retained for legal reasons. Analyze whether cascading deletion, restriction, anonymization, or soft deletion is most appropriate, and justify your choice.

Multiple Choice Questions with Answers

Question 1

Which statement best enforces entity integrity?

A. A foreign key referencing another table

B. A primary key that is unique and non-null

C. A default timestamp

D. A check allowing negative values

Correct Answer: B. A primary key that is unique and non-null

Question 2

What usually happens when an INSERT omits a column with a defined default?

A. The column receives the default value

B. The entire table is deleted

C. The column always receives NULL

D. The statement must always fail

Correct Answer: A. The column receives the default value

Question 3

Which predicate correctly finds rows where manager_id has no value?

A. manager_id = NULL

B. manager_id <> NULL

C. manager_id IS NULL

D. manager_id LIKE NULL

Correct Answer: C. manager_id IS NULL

Question 4

What is the main danger of an UPDATE without a WHERE clause?

A. It cannot use a primary key

B. It may modify every row

C. It always creates a foreign key

D. It automatically rolls back

Correct Answer: B. It may modify every row

Question 5

Which constraint prevents a product price from being negative?

A. CHECK (unit_price >= 0)

B. FOREIGN KEY (unit_price)

C. DEFAULT (unit_price >= 0)

D. PRIMARY KEY (unit_price >= 0)

Correct Answer: A. CHECK (unit_price >= 0)

Question 6

What does referential integrity require?

A. Every table must have exactly one column

B. Every foreign-key value must match a valid parent key or be permitted NULL

C. Every value must be positive

D. Every row must have the same primary key

Correct Answer: B. Every foreign-key value must match a valid parent key or be permitted NULL

Question 7

Which action makes related changes succeed together or fail together?

A. A transaction with commit or rollback

B. A column alias

C. A view definition

D. A sort operation

Correct Answer: A. A transaction with commit or rollback

Question 8

Why is a staging table useful in bulk loading?

A. It removes the need for constraints permanently

B. It permits validation before production modification

C. It guarantees that source data is correct

D. It prevents all concurrency

Correct Answer: B. It permits validation before production modification

Question 9

What is a key risk when using MERGE?

A. The source and target matching key may be ambiguous or duplicated

B. It cannot insert any rows

C. It always ignores constraints

D. It converts all values to text

Correct Answer: A. The source and target matching key may be ambiguous or duplicated

Question 10

Which practice helps detect an optimistic-concurrency conflict?

A. Ignoring the affected-row count

B. Updating with a version condition and checking whether one row changed

C. Removing the primary key

D. Using NULL for every version value

Correct Answer: B. Updating with a version condition and checking whether one row changed
SQL for Database
Chapter 8 of 15