Database Definition and Data Management

Introduction

A relational database is useful only when its structure and data-management rules accurately represent the real-world domain. Database definition establishes that structure: schemas, tables, columns, data types, keys, constraints, sequences, identity columns, indexes, and views. Data manipulation then creates, changes, and removes rows while preserving integrity.

This chapter focuses on MySQL 8.x and PostgreSQL 15+ syntax and behavior. The systems share the relational model and much SQL syntax, but they differ in identity generation, upsert statements, type systems, transaction behavior, and implementation details. Production developers must understand both the common standard and the vendor-specific extensions.

Learning Objectives

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

• create and alter schemas and tables;
• select appropriate data types;
• define primary keys, foreign keys, unique constraints, checks, and defaults;
• use sequences and identity columns;
• create indexes and views appropriately;
• perform INSERT, UPDATE, DELETE, and upsert operations;
• compare important MySQL and PostgreSQL behavior;
• design data changes that are safe, auditable, and transactionally consistent.

1. Database Definition as a Design Activity

Data definition language (DDL) describes database objects. Typical DDL commands include CREATE, ALTER, and DROP. Data manipulation language (DML) changes table contents through INSERT, UPDATE, DELETE, and, in vendor-specific forms, MERGE or upsert statements.

A database design has at least three layers:

1. Logical structure: entities, attributes, relationships, keys, and constraints.
2. Physical implementation: indexes, storage choices, generated values, and access paths.
3. Operational rules: permissions, transactions, migration procedures, backup, and auditing.

A table should represent one coherent subject. For example, an orders table should not repeat customer address data for every order unless denormalization is deliberate and justified. Constraints belong in the database when they express rules that must hold regardless of which application writes the data.

Schemas and namespaces

A schema is a namespace that groups tables, views, sequences, functions, and other objects. PostgreSQL supports multiple schemas within one database, for example sales.orders. PostgreSQL commonly uses the public schema by default. MySQL uses databases as the principal namespace; CREATE SCHEMA is effectively synonymous with CREATE DATABASE.

-- PostgreSQL
CREATE SCHEMA sales;

-- MySQL
CREATE DATABASE sales;

The commands are not interchangeable in operational meaning. PostgreSQL can contain several schemas inside one database, whereas a MySQL database is generally treated as a schema-level container.

2. Tables, Columns, and Data Types

A table consists of rows and columns. Every column should have a type that expresses permitted values and supports required operations. Choosing a type is a semantic decision, not merely a storage decision.

numeric(p,s) permits precision p total digits and scale s digits after the decimal point. For example, numeric(10,2) can represent values with up to eight digits before and two after the decimal point. Exact decimal types are preferable to floating-point types for currency because floating-point representation may introduce approximation.

Character length limits do not automatically solve validation problems. A varchar(20) restricts length, but it does not ensure that a value is a valid postal code or email address. Such domain rules require checks, application validation, or specialized database types.

Time zones require an explicit policy. PostgreSQL timestamptz stores an instant and displays it according to the session time zone. MySQL TIMESTAMP performs time-zone conversion between session and UTC in ways that differ from DATETIME, which generally stores a date-time value without such conversion. A globally distributed application should document whether a column represents an instant, a local civil time, or a date.

3. Keys and Integrity Constraints

A candidate key is a minimal attribute set that uniquely identifies a row. A primary key is the candidate key selected as the principal identifier. A foreign key references a key in another table and represents a relationship.

Important constraints include:

• NOT NULL: a value is required;
• UNIQUE: non-null values must be distinct, subject to vendor treatment of nulls;
• PRIMARY KEY: unique, non-null row identity;
• FOREIGN KEY: referenced-row integrity;
• CHECK: a Boolean condition that must hold;
• DEFAULT: a value supplied when an insert omits the column.

A foreign key does not merely document a relationship; it prevents orphaned references. Referential actions include RESTRICT, NO ACTION, CASCADE, SET NULL, and SET DEFAULT, though exact timing and support details vary. CASCADE is powerful but can delete or modify large related sets, so it should be selected intentionally.

A robust table definition might be:

CREATE TABLE customers (
    customer_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email varchar(320) NOT NULL UNIQUE,
    full_name varchar(150) NOT NULL,
    created_at timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CHECK (length(trim(full_name)) > 0)
);

CREATE TABLE orders (
    order_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id bigint NOT NULL,
    order_status varchar(20) NOT NULL DEFAULT 'pending',
    total_amount numeric(12,2) NOT NULL CHECK (total_amount >= 0),
    ordered_at timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT orders_customer_fk
        FOREIGN KEY (customer_id) REFERENCES customers(customer_id),
    CONSTRAINT orders_status_ck
        CHECK (order_status IN ('pending', 'paid', 'cancelled'))
);

The equivalent MySQL form uses AUTO_INCREMENT, CURRENT_TIMESTAMP, and commonly DATETIME:

CREATE TABLE customers (
    customer_id BIGINT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(320) NOT NULL UNIQUE,
    full_name VARCHAR(150) NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CHECK (CHAR_LENGTH(TRIM(full_name)) > 0)
);

Modern MySQL enforces CHECK constraints, but compatibility and behavior should be verified for the deployed version. PostgreSQL generally treats a check expression that evaluates to TRUE or UNKNOWN as passing; therefore, a check alone may not reject null. Use NOT NULL when null is prohibited.

4. Altering and Migrating Tables

ALTER TABLE changes an existing definition. Common operations include adding columns, changing defaults, renaming objects, and adding constraints.

ALTER TABLE customers ADD COLUMN phone varchar(30);
ALTER TABLE customers ALTER COLUMN phone SET NOT NULL;
ALTER TABLE customers RENAME COLUMN full_name TO name;

PostgreSQL and MySQL differ in syntax:

-- PostgreSQL
ALTER TABLE customers ALTER COLUMN email TYPE varchar(320);

-- MySQL
ALTER TABLE customers MODIFY COLUMN email VARCHAR(320) NOT NULL;

A migration should be treated as a controlled software release. Before adding NOT NULL, existing rows must be populated. Before adding a unique constraint, duplicates must be found and resolved. Large alterations may lock a table or consume substantial resources. Teams should use versioned migration scripts, backups, deployment testing, rollback planning, and observability.

Avoid relying on implicit column order. Always list columns in an INSERT, because future schema changes can otherwise cause failures or, worse, incorrect values.

5. Sequences, Identity Columns, and Generated Values

A sequence is an object that generates successive numeric values. In PostgreSQL it can be used explicitly:

CREATE SEQUENCE invoice_number_seq START WITH 1000 INCREMENT BY 1;
SELECT nextval('invoice_number_seq');

An identity column associates generated values with a table column:

CREATE TABLE invoices (
    invoice_id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    issued_on date NOT NULL DEFAULT CURRENT_DATE
);

GENERATED ALWAYS normally prevents explicit values unless an override is specified; BY DEFAULT permits an explicit value when supplied. Identity values are not guaranteed to be gapless. Rollbacks, failed transactions, caching, and concurrent requests can create gaps. If legal numbering requires gapless sequences, that is a separate, carefully designed allocation problem.

MySQL typically uses:

CREATE TABLE invoices (
    invoice_id BIGINT AUTO_INCREMENT PRIMARY KEY,
    issued_on DATE NOT NULL
);

AUTO_INCREMENT and PostgreSQL identity columns provide uniqueness-oriented generation, not proof of chronology, gaplessness, or business meaning. Expose generated identifiers only after the insert succeeds, and do not use them as security secrets.

6. Indexes and Query Performance

An index is an auxiliary structure that helps the database locate rows or produce ordered results. It improves some reads but consumes storage and adds work to inserts, updates, and deletes.

CREATE INDEX orders_customer_date_idx
    ON orders (customer_id, ordered_at DESC);

CREATE UNIQUE INDEX customers_email_uq
    ON customers (email);

A composite index is ordered by its leading columns. An index on (customer_id, ordered_at) is generally useful for conditions beginning with customer_id, but may not be equally useful for queries filtering only on ordered_at. Index design should follow actual query predicates, joins, ordering, and selectivity. Use EXPLAIN or EXPLAIN ANALYZE to inspect plans, while remembering that EXPLAIN ANALYZE executes the query in PostgreSQL.

Constraints often create indexes automatically. A primary key and unique constraint normally require uniqueness enforcement, but a foreign key does not necessarily create an index on the referencing column. Indexing foreign-key columns can improve joins and parent-row updates, especially for large tables.

PostgreSQL supports partial and expression indexes:

CREATE INDEX active_orders_idx
    ON orders (customer_id)
    WHERE order_status IN ('pending', 'paid');

MySQL supports specialized index features, but syntax and optimizer behavior differ. Indexes should be measured rather than added indiscriminately.

7. Views

A view is a stored query presented as a virtual table. It can simplify repeated logic, restrict exposed columns, and provide a stable interface to applications.

CREATE VIEW customer_order_totals AS
SELECT c.customer_id,
       c.email,
       COUNT(o.order_id) AS order_count,
       COALESCE(SUM(o.total_amount), 0) AS total_amount
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.email;

A normal view stores the query definition, not usually the result. Each query may be planned and executed against base tables. PostgreSQL also supports materialized views, which store results and must be refreshed. MySQL has no native materialized-view object; similar results require tables, scheduled jobs, or application-managed refreshes.

Views are not automatically security boundaries. Use explicit privileges, carefully designed security views, row-level security where supported, and least privilege. A view containing aggregated data may be updatable only under restricted conditions; joins, grouping, and computed columns often prevent direct updates.

8. Data Manipulation Language

INSERT

INSERT INTO customers (email, full_name)
VALUES ('[email protected]', 'Aisha Khan');

Multiple rows can be inserted in one statement:

INSERT INTO customers (email, full_name)
VALUES ('[email protected]', 'Li Chen'),
       ('[email protected]', 'Sam Rivera');

An insert must satisfy types, not-null rules, keys, checks, and foreign keys. In PostgreSQL, RETURNING can retrieve generated values:

INSERT INTO customers (email, full_name)
VALUES ('[email protected]', 'Aisha Khan')
RETURNING customer_id;

MySQL commonly retrieves the generated key through the client API or LAST_INSERT_ID() for the relevant connection.

UPDATE

An update without a WHERE clause affects every row. The safe pattern is to preview the predicate with SELECT, execute within a transaction when appropriate, and verify the affected-row count.

UPDATE orders
SET order_status = 'paid'
WHERE order_id = 501
  AND order_status = 'pending';

The additional status predicate makes the transition conditional and helps prevent invalid state changes. In concurrent workflows, transaction isolation, locking, or optimistic version columns may be required.

DELETE

DELETE FROM orders
WHERE order_id = 501
  AND order_status = 'cancelled';

Deletion can activate foreign-key actions. A soft-delete design, such as deleted_at, may be appropriate for auditability, but it requires every relevant query and uniqueness rule to account for inactive rows.

Upsert and MERGE

An upsert inserts a row or resolves a uniqueness conflict by updating an existing row. PostgreSQL uses ON CONFLICT:

INSERT INTO customers (email, full_name)
VALUES ('[email protected]', 'Aisha Khan')
ON CONFLICT (email)
DO UPDATE SET full_name = EXCLUDED.full_name
RETURNING customer_id;

EXCLUDED represents the proposed row. PostgreSQL also permits DO NOTHING.

MySQL uses ON DUPLICATE KEY UPDATE:

INSERT INTO customers (email, full_name)
VALUES ('[email protected]', 'Aisha Khan')
AS new
ON DUPLICATE KEY UPDATE full_name = new.full_name;

MySQL’s conflict may involve any relevant primary or unique key, not only the named key in PostgreSQL’s common form. MySQL also supports INSERT IGNORE, but suppressing errors can conceal data-quality problems.

The SQL standard MERGE expresses conditional insert, update, or delete logic based on a source relation. PostgreSQL 15+ supports MERGE; MySQL 8.x does not provide a general MERGE statement, so INSERT ... ON DUPLICATE KEY UPDATE, transactions, or multiple statements are used instead. A merge is not automatically safe: source duplicates, concurrent changes, and non-unique matching conditions can produce unexpected outcomes.

9. Transactions, Concurrency, and Safe Changes

A transaction groups operations into an all-or-nothing unit. The usual conceptual properties are atomicity, consistency, isolation, and durability (ACID).

BEGIN;
UPDATE orders SET order_status = 'paid'
WHERE order_id = 501 AND order_status = 'pending';
-- inspect affected rows
COMMIT;

Use ROLLBACK when validation fails. Transactions do not eliminate concurrency problems. Two transactions may read the same state and then overwrite each other. Techniques include row locks such as SELECT ... FOR UPDATE, optimistic version checks, appropriate isolation levels, and idempotent commands.

MySQL commonly uses the InnoDB engine for transactional tables. PostgreSQL uses multiversion concurrency control, in which readers and writers generally interfere less directly, although locks and serialization failures remain possible. Applications must handle deadlocks and retryable failures rather than assuming every transaction will succeed on its first attempt.

10. Illustrative Case: Course Registration System

Background. A university application stores students, courses, and registrations. A student may register for many courses, and each course may have many students. A student must not register for the same course twice.

Problem. An initial design stores course names directly in a registration table and allows duplicate rows. Reports become inconsistent, and deleting a course can leave meaningless registrations.

Analysis. The many-to-many relationship requires an associative table. The natural integrity rules are: each student and course must exist; the pair (student_id, course_id) must be unique; a registration status must use an allowed value; and enrollment dates are required.

Solution.

CREATE TABLE students (
    student_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    university_email varchar(320) NOT NULL UNIQUE
);

CREATE TABLE courses (
    course_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    course_code varchar(20) NOT NULL UNIQUE,
    title varchar(200) NOT NULL
);

CREATE TABLE registrations (
    student_id bigint NOT NULL REFERENCES students(student_id),
    course_id bigint NOT NULL REFERENCES courses(course_id),
    registered_at timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP,
    status varchar(15) NOT NULL DEFAULT 'active',
    PRIMARY KEY (student_id, course_id),
    CHECK (status IN ('active', 'dropped'))
);

Registration can use PostgreSQL upsert behavior when repeated requests must be idempotent:

INSERT INTO registrations (student_id, course_id)
VALUES (10, 42)
ON CONFLICT (student_id, course_id)
DO UPDATE SET status = 'active';

Outcome. The database prevents duplicate registrations and references to nonexistent entities. The application can retry an identical request without creating a second registration. Indexes created by the primary and foreign-key-related access patterns support common lookups; additional indexes should be validated with query plans.

Learning points. Model relationships explicitly, enforce invariant rules in the database, use conflict handling for idempotency, and distinguish technical identifiers from business attributes such as course codes.

11. Governance, Security, and Contemporary Practice

Database changes are part of an organization’s governance system. Access should follow least privilege: an application account should not automatically have permission to drop tables or alter schemas. Production changes should be reviewed, logged, tested, and associated with a migration version.

Personal data requires retention, access, correction, and deletion policies appropriate to applicable law and institutional rules. A view can reduce exposure, but it does not replace authorization. Avoid storing passwords directly; store passwords through a specialized password-hashing system. Parameterized queries are essential to prevent SQL injection; values should be bound through the database driver rather than concatenated into SQL text.

AI-assisted coding can generate plausible DDL and DML, but it may invent unsupported syntax, omit constraints, mishandle time zones, or produce destructive updates. Treat generated SQL as a draft: inspect execution plans, test migrations on representative data, review permissions, and verify behavior on the exact database version.

Best-Practice Checklist

• Give every table a clear purpose and every column a documented meaning.
• Use explicit primary and foreign keys.
• Apply NOT NULL, CHECK, and UNIQUE constraints where business invariants require them.
• Prefer exact numeric types for monetary values.
• Define time-zone semantics explicitly.
• List columns in every insert.
• Preview broad updates and deletes before executing them.
• Use transactions for related changes.
• Design indexes from measured query patterns.
• Treat migrations as versioned, tested software.
• Use least privilege and parameterized queries.
• Test vendor-specific syntax on the target MySQL or PostgreSQL version.

Chapter Summary

Database definition establishes the relational structures and integrity rules on which applications depend. Schemas organize objects; tables and data types express domain structure; primary, candidate, and foreign keys identify and connect rows. NOT NULL, UNIQUE, CHECK, defaults, and referential actions enforce important invariants. PostgreSQL identity columns and sequences and MySQL AUTO_INCREMENT generate identifiers, but none guarantees gapless or meaningful business numbering. Indexes improve selected access paths at a write and storage cost, while views provide reusable query interfaces and may support controlled data exposure.

DML must be explicit and safe: use column lists, restrictive predicates, transactions, affected-row checks, and appropriate concurrency controls. PostgreSQL commonly uses ON CONFLICT and supports MERGE; MySQL uses ON DUPLICATE KEY UPDATE and does not provide a general equivalent MERGE statement in MySQL 8.x. Sound database practice combines SQL knowledge with migration discipline, security, privacy, testing, and operational monitoring.

Key Terms

Schema: A namespace that groups database objects.

Table: A relational structure consisting of rows and columns.

Data type: A definition of permitted values and operations for a column.

Candidate key: A minimal attribute set that uniquely identifies a row.

Primary key: The selected principal identifier for table rows.

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

Constraint: A database rule that restricts invalid states.

Unique constraint: A rule requiring distinct non-null key values according to vendor behavior.

Check constraint: A Boolean condition applied to inserted or updated rows.

Default: A value supplied when an insert omits a column.

Sequence: An object that generates numeric values.

Identity column: A column whose values are generated by the database.

Index: An auxiliary structure that accelerates selected queries.

View: A stored query exposed as a virtual table.

Materialized view: A stored result of a query that requires refresh.

DDL: Commands that define or modify database objects.

DML: Commands that insert, update, or delete table data.

Upsert: An insert that resolves a uniqueness conflict by updating or ignoring.

Transaction: A unit of work governed by atomicity, consistency, isolation, and durability.

Migration: A versioned, controlled change to database structure or data.

Review Questions

Short Answer Questions

1. What is the difference between a primary key and a foreign key?
2. Why should monetary values normally use exact decimal types?
3. What is the purpose of a database index?
4. How does PostgreSQL ON CONFLICT support an upsert?
5. Why are identity and auto-increment values not necessarily gapless?

Long Answer Questions

1. Explain how keys and constraints work together to preserve entity and referential integrity.
2. Compare PostgreSQL schemas with MySQL databases as namespaces.
3. Discuss the advantages and costs of indexes, including composite indexes.
4. Compare PostgreSQL identity columns and sequences with MySQL AUTO_INCREMENT.
5. Explain how migrations should be planned and executed in a production environment.

Analytical / Application Questions

1. Design tables for an online library in which members borrow books. Identify primary keys, foreign keys, uniqueness rules, and checks.
2. An update intended for one customer changes every row. Diagnose the likely cause and propose a safe operational procedure.
3. A registration API may receive the same request several times because of network retries. Design an idempotent database solution and explain the role of a unique constraint and upsert.

Multiple Choice Questions with Answers

Question 1

Which constraint ensures that a column must contain a value?

A. CHECK

B. NOT NULL

C. FOREIGN KEY

D. DEFAULT

Correct Answer: B. NOT NULL

Question 2

In PostgreSQL, which expression represents the proposed row in an ON CONFLICT DO UPDATE clause?

A. NEW

B. SOURCE

C. EXCLUDED

D. TARGET

Correct Answer: C. EXCLUDED

Question 3

What is the principal purpose of a foreign key?

A. To sort rows physically

B. To generate numeric identifiers

C. To enforce a relationship to a referenced key

D. To encrypt column values

Correct Answer: C. To enforce a relationship to a referenced key

Question 4

Which PostgreSQL type is generally appropriate for exact monetary calculations?

A. real

B. double precision

C. numeric

D. boolean

Correct Answer: C. numeric

Question 5

What is a major cost of adding indexes?

A. They prevent all joins

B. They increase storage and write maintenance

C. They remove primary keys

D. They make constraints unnecessary

Correct Answer: B. They increase storage and write maintenance

Question 6

Which MySQL construct is commonly used for an upsert?

A. ON DUPLICATE KEY UPDATE

B. ON CONFLICT UPDATE

C. RETURNING CONFLICT

D. ALTER DUPLICATE ROW

Correct Answer: A. ON DUPLICATE KEY UPDATE

Question 7

What does a normal view usually store?

A. A permanent copy of every query result

B. A stored query definition

C. Only primary-key values

D. A transaction log

Correct Answer: B. A stored query definition

Question 8

Why should an UPDATE normally include a carefully tested WHERE clause?

A. Without it, the SQL cannot parse

B. Without it, all rows may be affected

C. It is required only by PostgreSQL

D. It creates an index automatically

Correct Answer: B. Without it, all rows may be affected

Question 9

Which statement about generated identifiers is correct?

A. They are always gapless

B. They necessarily represent business order

C. They can contain gaps after rollbacks or failures

D. They cannot be used as primary keys

Correct Answer: C. They can contain gaps after rollbacks or failures

Question 10

Which MySQL feature most closely corresponds to PostgreSQL identity-style automatic numbering?

A. AUTO_INCREMENT

B. MATERIALIZED VIEW

C. CHECK ONLY

D. EXCLUDED

Correct Answer: A. AUTO_INCREMENT