Ravindra BagaleCourses & study guides मराठी Track your progress

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

11.6 Constraints: NOT NULL, DEFAULT, CHECK, AUTO_INCREMENT

By Last updated:

Constraints (मर्यादा / नियम) mhanje database-level niyam – chukicha data table madhe yetach nahi, app madhe bug asla tari.

CREATE TABLE payments (
  payment_id INT AUTO_INCREMENT PRIMARY KEY,          -- 1, 2, 3 ... automatically
  student_id INT NOT NULL,                            -- must have a value
  amount     DECIMAL(10,2) NOT NULL CHECK (amount > 0),
  mode       VARCHAR(10) NOT NULL DEFAULT 'UPI'
             CHECK (mode IN ('UPI','Card','Cash','NetBanking')),
  paid_on    DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (student_id) REFERENCES students(student_id)
);

INSERT INTO payments (student_id, amount) VALUES (1, 5000);     -- mode=UPI, paid_on=now
INSERT INTO payments (student_id, amount) VALUES (1, -10);      -- rejected by CHECK
INSERT INTO payments (amount) VALUES (100);                     -- rejected: student_id NOT NULL
ALTER TABLE payments AUTO_INCREMENT = 1001;                     -- next id starts at 1001
Constraint Purpose
NOT NULL Value is required
DEFAULT Value used when none is given
CHECK Value must satisfy a condition (enforced in MySQL 8.0.16+ and MariaDB 10.2+)
AUTO_INCREMENT Automatic increasing number, usually for the primary key
UNIQUE, PRIMARY KEY, FOREIGN KEY Covered in section 11.5

Why this matters for security

Constraints are a second line of defence for integrity: even if a bug or attacker bypasses application validation, the database refuses negative payments or orphan rows. Also note that sequential AUTO_INCREMENT ids are easy to guess – if an app shows /invoice.php?id=1001 without an ownership check, changing it to 1002 is an IDOR attack (Part 11).

Ravindra Bagale's Tip

Old MySQL (before 8.0.16) used to quietly ignore CHECK constraints – many students think the rule is in place, but bad data keeps coming in. Check the version with SELECT VERSION(); and insert one wrong row to test whether the rule really works.

Practice task

Create payments, insert two valid rows, and try three invalid ones (negative amount, invalid mode, missing student). Note the error for each.

Extended teaching notes

Why this lesson matters

Cameras, sensors and plant controllers often ship with weak defaults. Segment them from IT, change passwords, block inbound internet, and watch outbound traffic.

Real examples

Global: Mirai showed what default passwords on routers/cameras can do at scale. Indian SME (sample): a factory puts PLCs on a separate VLAN reachable only via a jump host.

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)

  1. Write the lesson title, date and the exact lab target you are allowed to use.
  2. List three owned systems (VMs, apps, or cloud accounts) where this lesson applies.
  3. Run one safe check from the lesson on a lab system only. Paste the command and output.
  4. Write a two-sentence fix you would give a teammate.
  5. 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.