What is CHECK constraint?
Interviewer Expectations for this Question
01
Core Competency
Assesses fundamental understanding of SQL & Databases conventions, runtime behavior, and memory/performance considerations.
02
Evaluation Criteria
Hiring managers look for precision, avoidance of ambiguous jargon, and ability to explain trade-offs under real production conditions.
Comprehensive Model Answer
Verified Solution
A CHECK constraint ensures that all values stored in a column satisfy a specific boolean condition:
CREATE TABLE products (
product_id INT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
price DECIMAL(10, 2) CHECK (price > 0),
discount_percent INT CHECK (discount_percent BETWEEN 0 AND 100)
);
*(Note: MySQL introduced full enforcement of CHECK constraints in MySQL 8.0.16. Earlier MySQL 5.7 versions parsed the syntax but silently ignored enforcement.)*
Candidate Response Strategy & Interview Tips
- Start with a concise one-sentence summary: Deliver a direct, confident answer first before expanding into nuances.
- Demonstrate real-world trade-offs: Discuss where this approach excels and when you would avoid it in production systems.
- Discuss complexity & edge cases: Proactively explain time/space complexity or boundary conditions (null values, scale limits).
- Prepare for interviewer follow-ups: Technical hiring panels frequently probe deeper into concurrency, backward compatibility, or alternative libraries.