Designing Reliable Tables with Data Integrity Rules in MySQL

Course: MySQL Training

Topic: Designing Reliable Tables with Data Integrity Rules

Course Link: MySQL Training Course

Demo Link: Register for a Course Demo

1. Introduction to Data Integrity in MySQL

Data integrity refers to the accuracy, consistency, validity, and reliability of information stored in a database. A reliable database ensures that information follows defined rules whenever records are inserted, updated, or deleted.

MySQL provides several features to maintain data integrity, including primary keys, foreign keys, NOT NULL constraints, UNIQUE constraints, CHECK constraints, appropriate data types, and default values. These rules help prevent duplicate records, missing mandatory information, invalid values, and broken relationships between tables.

For example, an employee must have a unique employee ID, a product should not have a negative price, and an order should reference a customer who exists in the database. Database integrity rules help enforce these requirements consistently, even when data comes from multiple applications or database clients.

1.1 Why Data Integrity Is Important

• Accuracy: Ensures that stored information represents valid values.
• Consistency: Prevents contradictory information across related tables.
• Uniqueness: Prevents duplicate values where uniqueness is required.
• Completeness: Ensures that mandatory information is provided.
• Validity: Restricts values to acceptable ranges and categories.
• Referential integrity: Keeps relationships between tables valid.
• Maintainability: Makes databases easier to manage, troubleshoot, and extend.

2. Learning Objectives

After completing this topic, you should be able to:

• Understand the principles of reliable relational table design.
• Identify entities, attributes, relationships, and business rules.
• Choose appropriate MySQL data types.
• Define primary keys and foreign keys.
• Apply NOT NULL, UNIQUE, DEFAULT, and CHECK constraints.
• Understand referential actions such as ON DELETE and ON UPDATE.
• Design one-to-many and many-to-many relationships.
• Use normalization to reduce unnecessary duplication.
• Audit existing records for integrity problems.
• Diagnose common constraint errors.
• Build a reliable database schema for a real-world application.

3. Types of Data Integrity

Different integrity rules protect different aspects of a database. Understanding these categories helps you choose the appropriate constraint for each requirement.

4. Planning Reliable Tables

Before creating tables, understand what information the application needs to store and how that information will be used. Good table design begins with requirements rather than SQL syntax.

4.1 Identify Entities

An entity represents a person, object, event, or concept about which information is stored. Examples include customers, employees, departments, products, orders, and courses.

In a relational database, each major entity will generally have its own table.

CREATE TABLE customers (
    customer_id INT,
    customer_name VARCHAR(100),
    email VARCHAR(150)
);

This is a basic table definition. A production-ready design should also specify appropriate keys, mandatory fields, and uniqueness rules.

4.2 Identify Attributes

Attributes describe an entity. A customer may have a customer ID, name, email address, phone number, and registration date.

Choose attributes that are relevant to the entity. Avoid combining unrelated information into a single table simply because it is convenient.

4.3 Identify Relationships

Determine how entities relate to each other:

• One department can have multiple employees.
• One customer can place multiple orders.
• One order can contain multiple products.
• One student can enroll in multiple courses.

These relationships determine where foreign keys should be placed and whether a junction table is required.

4.4 Define Business Rules

Document the rules that the database must enforce. For example:

• Every employee must have an employee ID.
• Every required employee department must exist.
• Salary cannot be negative.
• Email addresses must be unique when the business requires one account per email.
• Order quantity must be greater than zero.
• Product stock cannot be negative.

Once the rules are clear, map each requirement to an appropriate database constraint or other validation mechanism.

5. Choosing Appropriate Data Types

Data types determine which kinds of values a column can store. Selecting suitable data types reduces unexpected conversions, improves clarity, and helps maintain data quality.

5.1 Use DECIMAL for Monetary Values

For prices, taxes, salaries, and other financial values that require exact decimal arithmetic, DECIMAL is generally preferable to FLOAT or DOUBLE.

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) NOT NULL
);

5.2 Store Phone Numbers as Text

Phone numbers may include country codes, leading zeros, spaces, and plus signs. They should generally be stored as text rather than as numeric values.

CREATE TABLE contacts (
    contact_id INT PRIMARY KEY,
    phone_number VARCHAR(25) NOT NULL
);

6. Entity Integrity Using PRIMARY KEY

A primary key uniquely identifies every row in a table. Primary-key values must be unique and cannot be NULL.

6.1 Defining a Primary Key

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL,
    salary DECIMAL(10,2) NOT NULL
);

The employee ID identifies each employee record. MySQL rejects duplicate employee IDs and NULL primary-key values.

6.2 Using AUTO_INCREMENT

AUTO_INCREMENT allows MySQL to generate an integer identifier when a new row is inserted without an explicit ID.

CREATE TABLE employees (
    employee_id INT AUTO_INCREMENT PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL,
    salary DECIMAL(10,2) NOT NULL
);

INSERT INTO employees (employee_name, salary)
VALUES ('Amit Sharma', 45000.00);

MySQL generates the employee ID based on the column's AUTO_INCREMENT configuration. Generated identifiers should not be assumed to be gap-free or equal to the number of rows in the table.

6.3 Best Practices for Primary Keys

• Choose a value that uniquely identifies each row.
• Prefer stable identifiers that do not change frequently.
• Consider a surrogate key when no suitable natural key exists.
• Avoid using mutable values such as names as primary keys.
• Use composite primary keys when multiple columns together form the appropriate identifier.

7. Preventing Missing Values with NOT NULL

The NOT NULL constraint ensures that a column cannot store NULL. It is suitable for information that must be supplied for every record.

CREATE TABLE customers (
    customer_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_name VARCHAR(100) NOT NULL,
    email VARCHAR(150) NOT NULL
);

This table requires a customer name and email. However, NOT NULL does not prohibit an empty string or whitespace-only text. If those values are invalid, additional validation is required.

7.1 NOT NULL Does Not Guarantee Uniqueness

NOT NULL prevents missing values but does not prevent duplicate values.

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(100) NOT NULL
);

Two rows can have the same department name unless a UNIQUE constraint is added.

8. Preventing Duplicates with UNIQUE

A UNIQUE constraint ensures that the values covered by the constraint do not duplicate another non-NULL key value. It is useful for usernames, email addresses, product codes, and other business identifiers when uniqueness is required.

CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(150) NOT NULL,
    CONSTRAINT uq_users_username UNIQUE (username),
    CONSTRAINT uq_users_email UNIQUE (email)
);

MySQL rejects inserts or updates that create duplicate values for these columns. For nullable UNIQUE columns, multiple NULL values are generally permitted in MySQL. Use NOT NULL as well when a value must always be provided.

8.1 Composite UNIQUE Constraint

A composite UNIQUE constraint prevents duplication of a combination of values.

CREATE TABLE course_enrollments (
    enrollment_id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT NOT NULL,
    course_id INT NOT NULL,
    CONSTRAINT uq_student_course
        UNIQUE (student_id, course_id)
);

This design prevents the same student-course combination from appearing more than once while allowing the student to enroll in other courses.

9. Domain Integrity Using CHECK Constraints

CHECK constraints validate whether a logical condition is satisfied. They can restrict numeric ranges, allowed categories, and relationships between columns in the same row.

CREATE TABLE products (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    stock_quantity INT NOT NULL,
    CONSTRAINT chk_product_price CHECK (price >= 0),
    CONSTRAINT chk_product_stock CHECK (stock_quantity >= 0)
);

These constraints prevent negative prices and stock quantities when enforced. They work alongside NOT NULL, which ensures that the values are present.

9.1 Restricting Allowed Categories

CREATE TABLE support_tickets (
    ticket_id INT AUTO_INCREMENT PRIMARY KEY,
    status VARCHAR(20) NOT NULL,
    CONSTRAINT chk_ticket_status
        CHECK (status IN ('Open', 'In Progress', 'Closed'))
);

The status must match one of the permitted values.

9.2 MySQL Version Compatibility

MySQL began enforcing CHECK constraints in version 8.0.16. Earlier versions generally accepted the syntax without enforcing the condition. Verify the server version and constraint enforcement status before relying on CHECK constraints for data protection.

10. Referential Integrity Using FOREIGN KEY

A foreign key connects a column or group of columns in one table to a referenced key in another table. It helps ensure that a child record refers to a valid parent record.

10.1 Creating Parent and Child Tables

CREATE TABLE departments (
    department_id INT AUTO_INCREMENT PRIMARY KEY,
    department_name VARCHAR(100) NOT NULL UNIQUE
);

CREATE TABLE employees (
    employee_id INT AUTO_INCREMENT PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL,
    department_id INT NOT NULL,
    CONSTRAINT fk_employee_department
        FOREIGN KEY (department_id)
        REFERENCES departments(department_id)
);

The departments table is the parent table, and employees is the child table. Each employee department ID must refer to an existing department.

10.2 Insert Parent Records First

INSERT INTO departments (department_name)
VALUES ('IT'), ('HR'), ('Finance');

After the departments have been created, employees can reference their actual department IDs.

INSERT INTO employees (employee_name, department_id)
VALUES ('Amit Sharma', 1);

This statement succeeds only if department ID 1 exists and all other applicable constraints are satisfied.

10.3 Handling a Missing Parent Record

INSERT INTO employees (employee_name, department_id)
VALUES ('Riya Patel', 9999);

If department ID 9999 does not exist, MySQL rejects the insert because it violates the foreign key constraint.

11. Choosing ON DELETE and ON UPDATE Rules

Foreign keys can specify what happens when a referenced parent row is deleted or its key is updated. The correct action depends on the relationship and business requirements.

11.1 Example of Referential Actions

CREATE TABLE employees (
    employee_id INT AUTO_INCREMENT PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL,
    department_id INT NOT NULL,
    CONSTRAINT fk_employee_department
        FOREIGN KEY (department_id)
        REFERENCES departments(department_id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE
);

Here, a department cannot be deleted while employees reference it. If a department ID is updated, the change is propagated to the child records.

11.2 When to Use CASCADE

CASCADE can be suitable for dependent records that have no meaningful existence without their parent. Use it carefully for business-critical information such as invoices, payments, historical orders, and audit records because cascading actions can affect many rows.

12. Default Values for Reliable Data Entry

A DEFAULT value is used when an INSERT statement omits a column that has a default. Defaults help establish predictable initial values and simplify common insert operations.

CREATE TABLE tasks (
    task_id INT AUTO_INCREMENT PRIMARY KEY,
    task_name VARCHAR(150) NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'Pending',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

If status is omitted, MySQL uses Pending. The created_at column receives the current timestamp according to its column definition and server behavior.

12.1 DEFAULT Does Not Replace NOT NULL

A default normally applies when the column is omitted from the INSERT statement. Explicitly supplying NULL to a NOT NULL column does not normally substitute the default; it results in an error under standard strict SQL behavior. SQL mode and statement details can affect error handling, so verify the behavior of the target environment.

13. Combining Multiple Integrity Rules

Reliable tables commonly combine several constraints because each one addresses a different requirement.

CREATE TABLE products (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    product_code VARCHAR(30) NOT NULL,
    product_name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    stock_quantity INT NOT NULL DEFAULT 0,
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    CONSTRAINT uq_product_code UNIQUE (product_code),
    CONSTRAINT chk_product_price CHECK (price >= 0),
    CONSTRAINT chk_product_stock CHECK (stock_quantity >= 0),
    CONSTRAINT chk_product_active CHECK (is_active IN (0, 1))
);

This table uses a primary key, mandatory columns, a unique product code, a default stock quantity, and CHECK constraints. In MySQL, BOOLEAN is a synonym for TINYINT(1); the final CHECK constraint documents and enforces the intended 0/1 values when CHECK enforcement is supported.

14. Designing One-to-Many Relationships

A one-to-many relationship occurs when one record in a parent table can be associated with multiple records in a child table. For example, one customer can place several orders.

CREATE TABLE customers (
    customer_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_name VARCHAR(100) NOT NULL,
    email VARCHAR(150) NOT NULL UNIQUE
);

CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    order_status VARCHAR(20) NOT NULL DEFAULT 'Pending',
    CONSTRAINT fk_orders_customer
        FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id),
    CONSTRAINT chk_order_status
        CHECK (order_status IN ('Pending', 'Processing', 'Shipped', 'Cancelled'))
);

Each order belongs to one customer. A customer can have multiple orders, but an order cannot reference a nonexistent customer.

15. Designing Many-to-Many Relationships

A many-to-many relationship exists when multiple records in one table can relate to multiple records in another table. For example, students can enroll in multiple courses, and each course can have multiple students.

A junction table represents the relationship.

CREATE TABLE students (
    student_id INT AUTO_INCREMENT PRIMARY KEY,
    student_name VARCHAR(100) NOT NULL
);

CREATE TABLE courses (
    course_id INT AUTO_INCREMENT PRIMARY KEY,
    course_name VARCHAR(100) NOT NULL
);

CREATE TABLE enrollments (
    student_id INT NOT NULL,
    course_id INT NOT NULL,
    enrollment_date DATE NOT NULL,
    CONSTRAINT pk_enrollments
        PRIMARY KEY (student_id, course_id),
    CONSTRAINT fk_enrollment_student
        FOREIGN KEY (student_id)
        REFERENCES students(student_id),
    CONSTRAINT fk_enrollment_course
        FOREIGN KEY (course_id)
        REFERENCES courses(course_id)
);

The composite primary key prevents the same student-course pair from being inserted twice. The foreign keys ensure that both the student and course exist.

16. Normalization and Data Integrity

Normalization organizes relational data to reduce unnecessary duplication and prevent insertion, update, and deletion anomalies. It supports data integrity, although constraints are still needed to enforce many business rules.

16.1 Example of a Poorly Designed Table

CREATE TABLE order_details_unstructured (
    order_id INT,
    customer_name VARCHAR(100),
    customer_email VARCHAR(150),
    product_1 VARCHAR(100),
    product_2 VARCHAR(100),
    product_3 VARCHAR(100)
);

This design has several weaknesses. The number of product columns is fixed, customer details may be repeated, and storing additional products requires changes to the table structure.

16.2 Improved Relational Design

CREATE TABLE customers (
    customer_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_name VARCHAR(100) NOT NULL,
    customer_email VARCHAR(150) NOT NULL UNIQUE
);

CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    CONSTRAINT fk_order_customer
        FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id)
);

CREATE TABLE products (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    CONSTRAINT chk_product_price CHECK (price >= 0)
);

CREATE TABLE order_items (
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,
    CONSTRAINT pk_order_items
        PRIMARY KEY (order_id, product_id),
    CONSTRAINT fk_item_order
        FOREIGN KEY (order_id)
        REFERENCES orders(order_id),
    CONSTRAINT fk_item_product
        FOREIGN KEY (product_id)
        REFERENCES products(product_id),
    CONSTRAINT chk_item_quantity CHECK (quantity > 0),
    CONSTRAINT chk_item_price CHECK (unit_price >= 0)
);

This design separates customers, orders, products, and order items into related tables. It supports multiple products per order and enforces relationships, valid quantities, and nonnegative prices.

17. Transactions and Data Integrity

A transaction groups multiple database operations into a logical unit of work. Transactions are important when related changes must succeed or fail together.

For example, transferring money between two accounts requires reducing one balance and increasing another. If only one operation succeeds, the balances become inconsistent. A transaction helps keep the changes together.

START TRANSACTION;

UPDATE accounts
SET balance = balance - 500
WHERE account_id = 1;

UPDATE accounts
SET balance = balance + 500
WHERE account_id = 2;

COMMIT;

This is a simplified example. A reliable implementation should validate that both accounts exist, check that the sender has sufficient funds, verify the affected rows, and roll back the transaction if any required step fails.

17.1 Using a CHECK Constraint for Account Balances

CREATE TABLE accounts (
    account_id INT PRIMARY KEY,
    balance DECIMAL(12,2) NOT NULL,
    CONSTRAINT chk_account_balance CHECK (balance >= 0)
);

The CHECK constraint prevents a balance from becoming negative when the condition is enforced. Transactions can group related updates, while constraints protect the validity of individual rows.

18. Auditing Existing Data

Before adding constraints to an existing database, inspect the data for duplicates, missing information, invalid ranges, and broken relationships.

18.1 Finding Duplicate Email Addresses

SELECT email, COUNT(*) AS duplicate_count
FROM customers
GROUP BY email
HAVING COUNT(*) > 1;

This query identifies repeated email values. If the column permits NULL, consider how NULL values should be treated in the audit.

18.2 Finding Missing Required Values

SELECT *
FROM customers
WHERE customer_name IS NULL
   OR email IS NULL;

This query identifies NULL values but does not find empty strings or whitespace-only text. Those cases require separate validation if they are invalid for the application.

18.3 Finding Invalid Numeric Values

SELECT *
FROM products
WHERE price < 0
   OR stock_quantity < 0;

18.4 Finding Orphaned Child Records

Legacy tables or imported data may contain references that do not match an existing parent record.

SELECT e.*
FROM employees AS e
LEFT JOIN departments AS d
    ON e.department_id = d.department_id
WHERE d.department_id IS NULL;

This query finds employees without a matching department. If department_id is nullable, the results also include rows where the department ID is NULL; filter those separately if required.

18.5 Inspecting Table Definitions

SHOW CREATE TABLE employees;

SHOW CREATE TABLE displays the table definition, including columns, keys, and constraints.

19. Modifying Tables Safely

Database requirements may change as applications evolve. ALTER TABLE can add columns, modify data types, and add or remove constraints. Before changing a table, check whether existing records satisfy the proposed rules.

19.1 Adding a Required Column

ALTER TABLE employees
ADD COLUMN hire_date DATE NULL;

Adding the column as nullable allows existing records to remain valid while the dates are populated.

SELECT *
FROM employees
WHERE hire_date IS NULL;

After correcting all missing values, make the column mandatory.

ALTER TABLE employees
MODIFY COLUMN hire_date DATE NOT NULL;

This change can fail if NULL values remain. Review the data before executing the modification.

19.2 Adding a UNIQUE Constraint

ALTER TABLE customers
ADD CONSTRAINT uq_customer_email UNIQUE (email);

Check for duplicate non-NULL email values before adding the constraint. Existing duplicates can prevent the operation from succeeding.

19.3 Adding a CHECK Constraint

ALTER TABLE products
ADD CONSTRAINT chk_product_price
CHECK (price >= 0);

Review existing prices first and confirm that the server version supports enforcement.

20. Common Integrity Errors and Troubleshooting

Integrity errors occur when an operation conflicts with a database rule. Identify the violated rule and correct the underlying data or design instead of removing constraints without a valid reason.

20.1 Compare Related Table Definitions

SHOW CREATE TABLE employees;
SHOW CREATE TABLE departments;

Check the column types, signedness where applicable, indexes, and key definitions. Foreign-key columns and referenced columns must meet MySQL's compatibility requirements.

20.2 Avoid Disabling Integrity Checks as a Routine Fix

Disabling foreign-key checks can permit inconsistent data to be loaded. Re-enabling the setting does not automatically scan and repair every row inserted while checks were disabled. If a controlled import requires disabling checks, explicitly validate the imported data before treating the process as complete.

21. Indexes and Data Integrity

Indexes help MySQL retrieve records efficiently and can also support integrity enforcement. Primary keys create primary-key indexes, while UNIQUE constraints create unique indexes. Foreign-key relationships have index requirements as well.

21.1 Creating an Index for Searches

CREATE INDEX idx_employees_department
ON employees(department_id);

This index may improve queries that filter or join employees by department. MySQL requires suitable indexing for foreign-key checks and may create a needed index automatically.

21.2 Ordinary Index Versus UNIQUE Index

Avoid creating indexes without a clear purpose. Excessive indexes consume storage and can slow INSERT, UPDATE, and DELETE operations because the indexes must also be maintained.

22. Practical Project: Order Management Database

This project combines primary keys, foreign keys, NOT NULL, UNIQUE, DEFAULT, and CHECK constraints to build a small order management database.

22.1 Create the Database

CREATE DATABASE order_management;
USE order_management;

22.2 Create the Customers Table

CREATE TABLE customers (
    customer_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_name VARCHAR(100) NOT NULL,
    email VARCHAR(150) NOT NULL,
    phone_number VARCHAR(25),
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT uq_customer_email UNIQUE (email)
);

The primary key identifies each customer, the name and email are mandatory, and the unique constraint prevents duplicate email values.

22.3 Create the Products Table

CREATE TABLE products (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    product_code VARCHAR(30) NOT NULL,
    product_name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    stock_quantity INT NOT NULL DEFAULT 0,
    CONSTRAINT uq_product_code UNIQUE (product_code),
    CONSTRAINT chk_product_price CHECK (price >= 0),
    CONSTRAINT chk_product_stock CHECK (stock_quantity >= 0)
);

The product code must be unique, the price cannot be negative, and stock quantity must be zero or greater. CHECK enforcement requires a compatible MySQL version.

22.4 Create the Orders Table

CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    order_status VARCHAR(20) NOT NULL DEFAULT 'Pending',
    CONSTRAINT fk_orders_customer
        FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE,
    CONSTRAINT chk_order_status
        CHECK (
            order_status IN (
                'Pending',
                'Processing',
                'Shipped',
                'Cancelled'
            )
        )
);

Every order must reference an existing customer. RESTRICT prevents deleting a customer while referenced orders remain. The CHECK constraint restricts order status to the specified categories.

22.5 Create the Order Items Table

CREATE TABLE order_items (
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,
    CONSTRAINT pk_order_items
        PRIMARY KEY (order_id, product_id),
    CONSTRAINT fk_items_order
        FOREIGN KEY (order_id)
        REFERENCES orders(order_id),
    CONSTRAINT fk_items_product
        FOREIGN KEY (product_id)
        REFERENCES products(product_id),
    CONSTRAINT chk_item_quantity
        CHECK (quantity > 0),
    CONSTRAINT chk_item_unit_price
        CHECK (unit_price >= 0)
);

The composite primary key prevents the same product from appearing more than once in an order. Foreign keys validate the order and product references. Quantity must be positive, and unit price cannot be negative.

22.6 Insert Sample Customers and Products

INSERT INTO customers (customer_name, email, phone_number)
VALUES
('Amit Sharma', '[email protected]', '9876500001'),
('Priya Verma', '[email protected]', '9876500002');

INSERT INTO products
(product_code, product_name, price, stock_quantity)
VALUES
('PRD101', 'Keyboard', 1200.00, 50),
('PRD102', 'Mouse', 600.00, 100),
('PRD103', 'Monitor', 8500.00, 20);

22.7 Create an Order

INSERT INTO orders (customer_id, order_status)
VALUES (1, 'Pending');

This assumes that the first customer has customer_id 1. In practice, use the actual ID generated by MySQL.

22.8 Add Products to the Order

INSERT INTO order_items
(order_id, product_id, quantity, unit_price)
VALUES
(1, 1, 2, 1200.00),
(1, 2, 1, 600.00);

This example assumes that order ID 1 and product IDs 1 and 2 exist. Replace these values with the actual IDs in your database.

22.9 Calculate the Order Total

SELECT
    order_id,
    SUM(quantity * unit_price) AS order_total
FROM order_items
GROUP BY order_id;

The query calculates each order's total from its order items. It does not automatically reduce product stock. Stock management requires additional application logic or a carefully designed transactional workflow.

22.10 Test the Integrity Rules

-- Duplicate email: expected to fail
INSERT INTO customers (customer_name, email)
VALUES ('Another Customer', '[email protected]');

-- Negative product price: expected to fail
INSERT INTO products
(product_code, product_name, price, stock_quantity)
VALUES ('PRD104', 'Invalid Product', -100.00, 10);

-- Nonexistent customer: expected to fail
INSERT INTO orders (customer_id)
VALUES (99999);

-- Zero order quantity: expected to fail
INSERT INTO order_items
(order_id, product_id, quantity, unit_price)
VALUES (1, 1, 0, 1200.00);

Run each invalid statement separately. Each should be rejected when the corresponding constraint is enforced and the referenced records are as described.

23. Exercises and Assignments

Exercise 1: Employee Database

• Create an employees table with employee ID, name, email, age, salary, and department ID.
• Use the employee ID as the primary key.
• Require the employee name and salary.
• Prevent duplicate employee email addresses.
• Add a CHECK constraint to prevent negative salaries.
• Reference a departments table through a foreign key.

Exercise 2: Student Enrollment Database

• Create students and courses tables.
• Create an enrollments junction table.
• Use a composite primary key to prevent duplicate student-course pairs.
• Add foreign keys to both parent tables.
• Require an enrollment date.

Exercise 3: Product Inventory

• Create a products table with product code, name, price, and stock quantity.
• Make the product code unique.
• Prevent negative prices and stock quantities.
• Set a default stock quantity of zero.
• Test valid and invalid inserts.

Exercise 4: Data Cleanup

• Create a table containing duplicate email addresses and invalid numeric values.
• Write queries to find duplicate and invalid records.
• Correct the data using suitable UPDATE or DELETE statements.
• Add constraints after verifying that the remaining data satisfies the rules.

Exercise 5: Foreign-Key Deletion Rules

• Create parent and child tables linked by a foreign key.
• Try deleting a parent row that has child records.
• Compare RESTRICT and CASCADE in a test database.
• Explain which behavior is appropriate for historical business records.

24. Interview Questions and Answers

Q1. What is data integrity?

Data integrity is the accuracy, consistency, validity, and reliability of information stored in a database.

Q2. What is entity integrity?

Entity integrity ensures that each row can be uniquely identified, usually through a primary key that is unique and non-NULL.

Q3. What is referential integrity?

Referential integrity ensures that foreign-key values reference valid records in the parent table, according to the defined relationship rules.

Q4. What is domain integrity?

Domain integrity restricts values to acceptable types, ranges, and categories using data types, NOT NULL, CHECK, and other suitable rules.

Q5. What is the difference between PRIMARY KEY and UNIQUE?

A primary key uniquely identifies each row and cannot contain NULL. A table can have only one primary-key constraint, though it can contain multiple columns. A table can have multiple UNIQUE constraints, and a nullable UNIQUE column can generally contain multiple NULL values in MySQL.

Q6. Why is NOT NULL important?

NOT NULL prevents a column from storing NULL. It does not automatically prevent empty strings or duplicate values.

Q7. What is a foreign key?

A foreign key links a child table to a referenced key in a parent table and helps prevent invalid relationships.

Q8. What is the purpose of a CHECK constraint?

A CHECK constraint enforces a logical condition on a row. MySQL began enforcing CHECK constraints in version 8.0.16 unless they are explicitly declared NOT ENFORCED.

Q9. What is the difference between ON DELETE CASCADE and RESTRICT?

CASCADE automatically deletes matching child records when a parent record is deleted. RESTRICT prevents deleting a parent while dependent child records remain.

Q10. What is a composite primary key?

A composite primary key consists of two or more columns whose combined values uniquely identify a row.

Q11. Why is normalization important?

Normalization organizes data into appropriate tables, reduces unnecessary duplication, and helps prevent insertion, update, and deletion anomalies.

Q12. What is the difference between a constraint and an index?

A constraint defines an integrity rule. An index supports efficient data retrieval and may also enforce uniqueness or support foreign-key requirements.

Q13. Can a foreign-key column contain NULL?

Yes, if the column permits NULL. A NULL foreign-key value generally does not require a matching parent row. If the relationship is mandatory, use NOT NULL.

Q14. How do you inspect table constraints?

Use SHOW CREATE TABLE or appropriate INFORMATION_SCHEMA tables to inspect constraint definitions and metadata.

Q15. Why should database constraints complement application validation?

Applications may contain bugs or be bypassed by scripts and other database clients. Database constraints provide centralized protection for the rules the database can enforce.

25. Quick Revision Checklist

• Identify entities, attributes, relationships, and business rules before creating tables.
• Choose suitable data types for each column.
• Use PRIMARY KEY to identify rows uniquely.
• Use NOT NULL for mandatory values.
• Use UNIQUE for values or combinations that must not be duplicated.
• Use CHECK for supported row-level validation rules.
• Use FOREIGN KEY to protect relationships between tables.
• Choose ON DELETE and ON UPDATE actions carefully.
• Use DEFAULT values where they are appropriate.
• Normalize tables to reduce unnecessary duplication.
• Use transactions for related operations that must succeed or fail together.
• Audit existing data before adding constraints.
• Inspect table definitions when troubleshooting integrity errors.
• Test valid values, invalid values, and boundary conditions.
• Review indexes for performance and integrity requirements.
• Keep application validation consistent with database rules.

26. Conclusion

Designing reliable tables with data integrity rules is a fundamental skill in MySQL database development. A well-designed schema uses appropriate data types, primary keys, unique constraints, mandatory fields, CHECK constraints, and foreign keys to protect the quality and consistency of stored information.

These rules work best when they are based on clear business requirements and tested with realistic data. Normalization, indexing, transaction management, careful schema changes, and regular data audits further improve reliability and maintainability.

To learn more about SQL queries, relational database design, constraints, and practical MySQL development, visit the JustAcademy MySQL Training Course. You can also register for a course demo to explore the training in more detail.