gaurbprajapati

Posted on Sep 30

MySQL Storage Engines, Transactions, and Foreign Keys

#webdev #programming #database #mysql

1. What is a Storage Engine in MySQL?

A storage engine in MySQL determines how data is stored, accessed, and managed internally.

Every table in MySQL uses a storage engine.
The storage engine defines features, performance, and behavior of the table.

Common storage engines:

Engine	Supports Transactions?	Supports Foreign Keys?	Locking Type	Notes
InnoDB	✅ Yes	✅ Yes	Row-level	Default in modern MySQL, crash-safe, best for concurrent writes
MyISAM	❌ No	❌ No	Table-level	Older engine, read-heavy workloads, no transactions

2. ENGINE=InnoDB Explained

When you create a table:

CREATE TABLE test_scripts_t (
    script_id BIGINT AUTO_INCREMENT PRIMARY KEY,
    script_name VARCHAR(255) NOT NULL
) ENGINE=InnoDB;

ENGINE=InnoDB tells MySQL to use InnoDB storage engine for this table.
Benefits:

Transactions – all-or-nothing operations (START TRANSACTION, COMMIT, ROLLBACK)
Foreign keys – enforce relational integrity
Row-level locking – allows multiple scripts to insert/update simultaneously
Crash safety – automatic recovery if MySQL crashes

2.1 What happens if you don’t specify ENGINE?

MySQL uses its default engine (check with SHOW ENGINES;).
Modern MySQL → default is usually InnoDB → everything works.
Old MySQL → default may be MyISAM → you lose transactions and foreign key support.

Example problem if default is MyISAM:

You define a foreign key, but it is ignored.
You try ROLLBACK after a failure → MySQL does nothing.
Multiple concurrent inserts may block each other (table-level locking).

3. Transactions (Beginner-Friendly)

A transaction is a set of SQL statements executed as a single unit:

Either all succeed → changes saved (COMMIT)
Or any fail → all undone (ROLLBACK)

3.1 Example: Bank Transfer

Tables:

CREATE TABLE accounts (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    balance INT
) ENGINE=InnoDB;

Transaction to transfer $100 from Alice to Bob:

START TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE name='Alice';
UPDATE accounts SET balance = balance + 100 WHERE name='Bob';

COMMIT;

✅ Both updates succeed → transfer completed
❌ If Bob’s row is missing → ROLLBACK → Alice’s balance remains unchanged

Important: Transactions only work with InnoDB.

4. Foreign Keys (FK)

A foreign key is a column that links one table to another, ensuring relational integrity.

Example: orders table has user_id referencing users.user_id
Prevents creating orphan records (e.g., orders for non-existent users)

4.1 Example: Users and Orders

Users table:

CREATE TABLE users (
    user_id INT PRIMARY KEY,
    name VARCHAR(50),
    email VARCHAR(100)
) ENGINE=InnoDB;

Orders table with FK:

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    user_id INT,
    product VARCHAR(50),
    amount INT,
    FOREIGN KEY (user_id) REFERENCES users(user_id)
        ON DELETE CASCADE
) ENGINE=InnoDB;

ON DELETE CASCADE → deletes orders automatically if user is deleted
You cannot insert an order with a user_id that doesn’t exist in users

4.2 Foreign Key in Action

Insert valid data:

INSERT INTO users (user_id, name, email) VALUES (1, 'Alice', 'alice@mail.com');
INSERT INTO orders (order_id, user_id, product, amount) VALUES (101, 1, 'Laptop', 1200);

✅ Works fine.

Insert invalid data:

INSERT INTO orders (order_id, user_id, product, amount) VALUES (102, 99, 'Phone', 500);

❌ Error! user_id = 99 does not exist.

Delete a user:

DELETE FROM users WHERE user_id = 1;

Because of ON DELETE CASCADE, Alice’s order is also deleted automatically.

4.3 What if not using InnoDB?

MyISAM ignores foreign key definitions
You can insert orphan rows without error
No cascading delete → manual cleanup required

5. Beginner Analogy

Concept	Analogy
Transaction	Writing multiple entries in a notebook with erasable ink → undo if mistake
Foreign Key	Linking an order to a valid user ID card → cannot create an order for a fake user
InnoDB	Notebook with erasable ink, checks all references automatically
MyISAM	Permanent ink → mistakes cannot be undone, references not checked

✅ Key Takeaways for Beginners

Always use InnoDB unless you have a very specific reason.
Transactions allow safe, all-or-nothing operations.
Foreign keys enforce data integrity across tables.
MyISAM or non-InnoDB tables cannot use FK or transactions.
For logging automation test executions, InnoDB + FK + transactions is the safe design.

DEV Community