Using Rollback to Handle Constraint Violations in MySQL

Rollback on Constraint Violation

Write a MySQL query to rollback a transaction if a constraint violation occurs.

Solution:

-- Start a new transaction
START TRANSACTION;

-- Insert a new employee into the Employees table
-- This tries to add an employee with EmployeeID = 1, Name = 'Kamilla Njord', and DepartmentID = 2.
INSERT INTO Employees (EmployeeID, Name, DepartmentID) VALUES (1, 'Kamilla Njord', 2);

-- Simulate a constraint violation
-- If EmployeeID = 1 already exists and is a PRIMARY KEY or UNIQUE, this insertion will fail.
-- The ROLLBACK statement will then undo any changes made in the transaction.
ROLLBACK;

Explanation:

• Purpose of the Query:
• The goal is to demonstrate how to rollback a transaction when a constraint violation occurs.
• Key Components:
• ROLLBACK: Undoes the transaction if an error occurs.
• Why use Rollback?:
• Rollback ensures that invalid changes are not applied to the database.
• Real-World Application:
• For example, in a registration system, you might rollback a transaction if a unique constraint is violated.

For more Practice: Solve these Related Problems:

• Write a MySQL query that attempts to insert a duplicate primary key in a table and rolls back the transaction upon failure.
• Write a MySQL query that tries to insert an order with a non-existing customer ID and rolls back the transaction on violation.
• Write a MySQL query that enforces a foreign key constraint and rolls back if a referenced record does not exist.
• Write a MySQL query that inserts a record with a NOT NULL constraint violation and ensures the rollback mechanism is triggered.

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous MySQL Exercise: Use Transactions to Ensure Atomicity.
Next MySQL Exercise: Set Isolation Level to Repeatable Read.