11. MySQL Part 2: UPDATE, ALTER, DELETE, Keys, Constraints and Users

By Ravindra Bagale Last updated: 15 October 2026

Chala mitrano, kaal aapan data vachayla shiklo. Aaj data badlayla, table chi rachna badlayla, data kaadhayla aani database la niyam (keys, constraints) lavayla shikuya. Shevti users aani GRANT – kon kay karu shakto. Ha chapter khup important aahe, lakshat theva: ek chukicha UPDATE kiwa DELETE purna table udavto. Mhanun safe update mode aani transactions pan shikuya. Ghabru naka, pratyek query aadhi SELECT ne test karaychi – hi savay aajpasun.

What you will learn in this chapter

UPDATE with WHERE, and testing with SELECT first
Safe update mode (SQL_SAFE_UPDATES, error 1175) and transactions
ALTER TABLE: add, modify, rename and drop columns, rename tables
DELETE, TRUNCATE and DROP, and the exact differences between them
All keys: primary, foreign, unique, composite, candidate, super and alternate
Constraints: NOT NULL, DEFAULT, CHECK, AUTO_INCREMENT
Creating users, GRANT, REVOKE and least privilege; backup with mysqldump

Concepts in this chapter

The chapter recap is at the end of the last concept page.

Chapter 11: MySQL part 2: update, alter, delete, keys — practice tasks

Open chapter lessons · Full guided lab: mysql backup restore mysqldump (not repeated here)

11.1 Back up before you UPDATE

EasyBanking

Why: One wrong UPDATE without WHERE can change every customer's balance.

What: Back up table ship (from ch10 task), then run a safe UPDATE.

How:

docker exec db mysqldump -uroot -plabpass lab ship > ship.sql
START TRANSACTION;
UPDATE ship SET city='Pune' WHERE id=1;
SELECT * FROM ship; then COMMIT;

Expected output: Only row 1 changes; ship.sql exists and is not empty.

Hint

Use a transaction so you can ROLLBACK.

Answer / how to check

grep -c INSERT ship.sql ≥ 1.
Only id=1 shows Pune.

11.2 Catch a missing WHERE with safe-updates

MediumIT

Why: MySQL can refuse dangerous updates for you.

What: Turn on sql_safe_updates and try an UPDATE with no WHERE.

How:

SET sql_safe_updates=1;
UPDATE ship SET amount=0;
Read the error.

Expected output: ERROR 1175: safe update mode.

Hint

Start the client with --safe-updates to make it default.

Answer / how to check

No rows changed: SELECT SUM(amount) FROM ship; is unchanged.

11.3 Add a foreign key for a hospital

MediumHealthcare

Why: Keys stop orphan records, like a visit for a patient who doesn't exist.

What: Create patients and visits tables linked by a key.

How:

CREATE TABLE pat(id INT PRIMARY KEY, name VARCHAR(40));
CREATE TABLE visit(id INT PRIMARY KEY, pat_id INT, FOREIGN KEY(pat_id) REFERENCES pat(id));
INSERT INTO pat VALUES(1,'Kavya Rao');
INSERT INTO visit VALUES(1,99);

Expected output: ERROR 1452: foreign key constraint fails.

Hint

Patient 99 doesn't exist.

Answer / how to check

INSERT INTO visit VALUES(1,1); works.

Extended teaching notes

Why this lesson matters

Use this lesson to strengthen systems you own: understand the risk, practise safely in a lab, then write and apply the fix.

Apply the fix across stacks

Same class of bug, same idea in every language.

Java / Spring Boot

Parameterise queries, encode output, CSRF tokens, limited Actuator, dependency scanning.

ASP.NET Core

EF Core / parameterised SQL, Razor encoding, HTTPS, Data Protection.

Node.js / Express

helmet, parameterised SQL, npm audit, avoid eval and shell concat.

Python / Django / Flask / FastAPI

ORM, autoescape, pip-audit, Bandit; never shell=True with user input.

PHP / Laravel

PDO/Eloquent, Blade escaping, composer audit.

Go / Ruby

database/sql placeholders / ActiveRecord; html/template / ERB escaping; govulncheck / Brakeman.

nginx / Apache / Tomcat

Hide versions, TLS, security headers, least-open ports.

MySQL / PostgreSQL / MongoDB / Redis

Private networks, auth on, least-privilege users, no 0.0.0.0/0.

AWS / Azure / GCP

Roles not long-lived keys, private data stores, cloud audit logs on.

Mini case study

Suppose we are on a purple-team exercise for an Indian mid-size company (sample data). Red notes a technique from this lesson against a lab clone. Blue must show the control that stops it, the log that would have fired, and the ticket that hardens production. That is the standard this course trains you for.

Defender's worksheet (do this today)

Write the lesson title, date and the exact lab target you are allowed to use.
List three owned systems (VMs, apps, or cloud accounts) where this lesson applies.
Run one safe check from the lesson on a lab system only. Paste the command and output.
Write a two-sentence fix you would give a teammate.
Name one related public advisory (CERT-In, CISA, NVD, or a vendor bulletin) with its date.

Indian workplace (sample data)

Suppose we are a junior analyst at Infosys, TCS, Wipro or a fintech SOC in Bengaluru/Pune. Turn this lesson into one checklist item with an owner, a due date and a place to store evidence (ticket + screenshot).

Global practice

Compare your notes with OWASP, CIS Benchmarks, NIST CSF/IR guidance or a cloud Well-Architected security question. Write five lines on what overlaps.

Everyday analogy

Explain the risk and the fix to a family member without jargon. If they can repeat the warning, you understand it well enough to teach juniors.

Key terms

Control: a safeguard (patch, config, process, detection).
Finding: a weakness backed by evidence.
Remediation: the fix plus how you verified it.
Scope: systems you have written permission to touch.
Least privilege: only the access the task needs.
Defence in depth: several layers so one failure does not end the story.

Common mistakes

Skipping written permission "just for a quick scan".
Practising on hostel, office or public targets without authorisation.
Collecting tool output without writing the fix.
Copying attack steps onto production to "verify". Defenders verify with versions, configs and patches.
Publishing screenshots that contain secrets, cookies or internal hostnames.

Recap

Evidence, fix, owner, date.
Own lab only.
Fix > spectacle.