Constraints let the database enforce rules about valid rows and relationships. They are important even when an application validates inputs, because data may be written by scripts, admin tools, background jobs, or other services.
Key Takeaways at a Glance:
- •PRIMARY KEY enforces entity integrity (strictly unique + NOT NULL) and creates the table's clustered index in InnoDB.
- •FOREIGN KEY enforces referential integrity, preventing child records from referencing non-existent parent rows.
- •Cascading rules: ON DELETE CASCADE automatically purges child rows when the parent row is deleted; ON DELETE RESTRICT blocks deletion.
- •CHECK constraints enforce domain rules (e.g. CHECK (age >= 18)); UNIQUE constraints enforce uniqueness while permitting NULLs.
In Simple Words (The Mental Model)
Constraints are the automated security guard rails of your database. If a developer accidentally writes buggy backend code that tries to insert a negative salary, or delete a department that still has 50 employees, the database rejects the command and throws an error.
Real-World Analogy
Think of an electrical outlet with a safety prong. A two-prong plug won't fit into a ground fault outlet unless the grounding pin is aligned (Foreign Key check). If you try to plug in a 500-volt device into a 120-volt socket, the circuit breaker trips instantly (CHECK constraint).