MySQL Database Design, Relationships, and Advanced Querying Techniques

Foreign Key Constraints and Referential Integrity

Establishing Table Relationships

Foreign keys enforce referential integrity by ensuring that values in a child table correspond to existing records in a parent table. This prevents orphaned records and maintains data consistency across related entiteis.

Creating Tables with Foreign Keys

When designing a schema, define the parent table first, then reference its primary key in the child table during creation.

-- Define the parent entity
CREATE TABLE department (
    dept_id INT PRIMARY KEY AUTO_INCREMENT,
    dept_name VARCHAR(50) NOT NULL
);

-- Define the child entity with a foreign key
CREATE TABLE employee (
    emp_id INT PRIMARY KEY AUTO_INCREMENT,
    emp_name VARCHAR(50) NOT NULL,
    dept_id INT,
    CONSTRAINT fk_emp_dept FOREIGN KEY (dept_id) REFERENCES department(dept_id)
);

Inserting data respects these constraints. Attempting to add an employee with a non-existent dept_id will result in an error. Similarly, deleting a department that has associated employees will be blocked to prevent data loss.

Managing Constraints

You can add or remove foreign keys after table creation using ALTER TABLE.

-- Remove an existing constraint
ALTER TABLE employee DROP FOREIGN KEY fk_emp_dept;

-- Add a constraint to an existing table
ALTER TABLE employee 
    ADD CONSTRAINT fk_emp_dept 
    FOREIGN KEY (dept_id) REFERENCES department(dept_id);

Cascading Actions

To automate data synchronization, configure cascade rules. These rules dictate how changes in the parent table effect child records.

ALTER TABLE employee DROP FOREIGN KEY fk_emp_dept;

-- Configure cascading updates and deletes
ALTER TABLE employee 
    ADD CONSTRAINT fk_emp_dept 
    FOREIGN KEY (dept_id) REFERENCES department(dept_id)
    ON UPDATE CASCADE
    ON DELETE CASCADE;

With ON DELETE CASCADE, removing a department automatically removes all associated employees. With ON UPDATE CASCADE, changing a department's primary key propagates the change to all child rows.

Multi-Table Relationship Patterns

One-to-One Relationships

Use this pattern when two entities share a single instance correspondence, such as a citizen and their passport. The foreign key is typically placed in one table with a unique constraint.

CREATE TABLE citizen (
    citizen_id INT PRIMARY KEY AUTO_INCREMENT,
    full_name VARCHAR(100) NOT NULL
);

CREATE TABLE passport (
    passport_id INT PRIMARY KEY AUTO_INCREMENT,
    passport_number VARCHAR(20) UNIQUE,
    citizen_id INT UNIQUE,
    CONSTRAINT fk_passport_citizen FOREIGN KEY (citizen_id) REFERENCES citizen(citizen_id)
);

One-to-Many Relationships

This is the most common pattern. The "many" side holds the foreign key referencing the "one" side. Examples include authors writing multiple books, or categories containing multiple products.

CREATE TABLE author (
    author_id INT PRIMARY KEY AUTO_INCREMENT,
    author_name VARCHAR(100) NOT NULL
);

CREATE TABLE book (
    book_id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(200) NOT NULL,
    author_id INT,
    CONSTRAINT fk_book_author FOREIGN KEY (author_id) REFERENCES author(author_id)
);

Many-to-Many Relationships

Complex relationships require an associative (junction) table. This table contains foreign keys referencing both related tables, often forming a composite primary key.

CREATE TABLE musician (
    musician_id INT PRIMARY KEY AUTO_INCREMENT,
    musician_name VARCHAR(100) NOT NULL
);

CREATE TABLE instrument (
    instrument_id INT PRIMARY KEY AUTO_INCREMENT,
    instrument_type VARCHAR(50) NOT NULL
);

CREATE TABLE musician_instrument (
    relation_id INT PRIMARY KEY AUTO_INCREMENT,
    musician_id INT,
    instrument_id INT,
    CONSTRAINT fk_mi_musician FOREIGN KEY (musician_id) REFERENCES musician(musician_id),
    CONSTRAINT fk_mi_instrument FOREIGN KEY (instrument_id) REFERENCES instrument(instrument_id)
);

Advanced Query Operations

Cartesian Products

Listing multiple tables in the FROM clause without a join condition produces a Cartesian product, combining every row from the first table with every row from the second. This is usually unintended and must be filtered using join conditions.

SELECT * FROM department, employee; -- Produces cross join result

Inner Joins

Inner joins return only rows where a match exists in both tables based on the join condition.

-- Explicit Inner Join
SELECT 
    e.emp_name, 
    d.dept_name 
FROM 
    employee e
INNER JOIN 
    department d 
ON 
    e.dept_id = d.dept_id;

-- Implicit Inner Join
SELECT 
    e.emp_name, 
    d.dept_name 
FROM 
    employee e, department d 
WHERE 
    e.dept_id = d.dept_id;

Outer Joins

Outer joins preserve rows from one table even if no matching row exists in the other.

Left Outer Join

Returns all rows from the left table and matching rows from the right. Non-matching columns from the right table are NULL.

SELECT 
    d.dept_name, 
    e.emp_name 
FROM 
    department d
LEFT JOIN 
    employee e 
ON 
    d.dept_id = e.dept_id;

Right Outer Join

Returns all rows from the right table and matching rows from the left. Non-matching columns from the left table are NULL.

SELECT 
    e.emp_name, 
    d.dept_name 
FROM 
    department d
RIGHT JOIN 
    employee e 
ON 
    d.dept_id = e.dept_id;

Subqueries

Subqueries are queries nested within another query, often used to provide data for filtering or table references.

Single-Row, Single-Column Result

Used with comparison operators in the WHERE clause.

-- Find employees earning the maximum salary
SELECT 
    emp_name, 
    salary 
FROM 
    employee 
WHERE 
    salary = (SELECT MAX(salary) FROM employee);

Multi-Row, Single-Column Result

Used with IN or NOT IN operators.

-- Find books written by authors from specific names
SELECT 
    title 
FROM 
    book 
WHERE 
    author_id IN (
        SELECT author_id 
        FROM author 
        WHERE author_name IN ('J.K. Rowling', 'George Orwell')
    );

Multi-Row, Multi-Column Result

Acts as a derived table in the FROM clause.

-- Join employee data with a filtered subset of sales records
SELECT 
    e.emp_name, 
    s.total_sales 
FROM 
    employee e
JOIN 
    (SELECT emp_id, SUM(amount) as total_sales FROM sales WHERE status='COMPLETED' GROUP BY emp_id) s 
ON 
    e.emp_id = s.emp_id;

Self-Joins

Self-joins allow a table to be joined with itself, useful for hierarchical data like employee-manager relationships.

CREATE TABLE staff (
    staff_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    manager_id INT
);

-- Query staff and their direct managers
SELECT 
    s.name AS employee_name, 
    m.name AS manager_name 
FROM 
    staff s
LEFT JOIN 
    staff m 
ON 
    s.manager_id = m.staff_id;

Virtual Tables (Views)

View Fundamentals

Views are saved query results that act as virtual tables. They do not store data physically but provide a dynamic representation of underlying table data. Views simplify complex queries, enhance security by restricting column access, and abstract schema changes.

Creating Views

-- Create a view combining city and country data
CREATE TABLE region (
    region_id INT PRIMARY KEY,
    region_name VARCHAR(50)
);

CREATE TABLE branch (
    branch_id INT PRIMARY KEY,
    branch_name VARCHAR(50),
    region_id INT,
    FOREIGN KEY (region_id) REFERENCES region(region_id)
);

-- Standard view creation
CREATE VIEW branch_details AS
SELECT 
    b.branch_id,
    b.branch_name,
    r.region_name
FROM 
    branch b
JOIN 
    region r 
ON 
    b.region_id = r.region_id;

-- View with explicit column names
CREATE VIEW branch_report (id, location, territory) AS
SELECT 
    b.branch_id,
    b.branch_name,
    r.region_name
FROM 
    branch b
JOIN 
    region r 
ON 
    b.region_id = r.region_id;

Querying and Modifying Views

Views can be queried like standard tables. Updates through a view propagate to the base table if the view is updatable.

-- Query the view
SELECT * FROM branch_details;

-- Check view definition
SHOW CREATE VIEW branch_details;

-- Update data via view
UPDATE branch_details SET branch_name = 'Main Office' WHERE branch_name = 'HQ';

Altering and Dropping Views

Modify the structure of an existing view or remove it when no longer needed.

-- Alter view structure
ALTER VIEW branch_details AS
SELECT 
    b.branch_id,
    b.branch_name,
    r.region_name,
    b.established_date
FROM 
    branch b
JOIN 
    region r 
ON 
    b.region_id = r.region_id;

-- Drop a view
DROP VIEW branch_details;
DROP VIEW IF EXISTS branch_report;

Data Backup and Recovery

Command-Line Operations

Use mysqldump for logical backups. This generates SQL statements that can recreate the database objects and data.

Exporting Data

# Dump entire database to file
mysqldump -u root -p production_db > /backups/production_db_backup.sql

Importing Data

Restore data by executing the SQL file within the MySQL client.

-- Login to MySQL
mysql -u root -p

-- Create target database
CREATE DATABASE production_db;
USE production_db;

-- Import data
SOURCE /backups/production_db_backup.sql;

Graphical Tools

Database management tools provide visual interfaces for backup and restoration. These utilities automate the export of schema and data into files and facilitate restoration through dialog boxes, often allowing selective restoration of specific tables or objects.

Tags: MySQL Database Design Foreign Keys SQL Joins Subqueries

Posted on Tue, 13 Oct 2026 16:43:45 +0000 by fallen00sniper

Freaks City