Database constraints fundamentals are rules that protect data quality and prevent mistakes. Think of constraints like guardrails on a highway—they prevent your car (data) from going off the road (violating business rules). A database without constraints is like a school without rules: chaos results. Understanding constraints is essential for designing reliable systems.
Understanding Database Constraints Fundamentals
Constraints enforce rules at the database level, not just in application code. An application can have bugs; the database cannot. If application validation fails, bad data gets stored. If database constraints fail, bad data is rejected. Constraints fundamentals: “Trust but verify.” Never rely only on application validation.
Four main constraints protect databases: PRIMARY KEY (uniqueness and non-null), FOREIGN KEY (referential integrity), UNIQUE (no duplicates), and CHECK (valid values). Each solves a different problem.
PRIMARY KEY: Unique Identifier for Each Row
A PRIMARY KEY uniquely identifies each row. No two rows can have the same PK, and PK cannot be NULL. Usually, you use an auto-incrementing integer (surrogate key) or a business-meaningful column (natural key like email). PRIMARY KEY automatically creates an index, making lookups fast.
In layman terms: PRIMARY KEY is like a passport number. Every person has a unique number; no two people share one. The number never changes (unless you get a new passport, which is rare).
FOREIGN KEY: Relationships Between Tables
A FOREIGN KEY ensures that a value in one table must exist in another table. ORDER.customer_id must exist in CUSTOMER table. If you try to create an order for a non-existent customer, the database rejects it. FOREIGN KEY prevents orphaned records (orders with invalid customer IDs).
In layman terms: FOREIGN KEY is like a security checkpoint. You cant enter a club unless you have a valid membership card (exist in the members table). Without a membership, youre denied entry.
UNIQUE Constraint: No Duplicates
UNIQUE ensures no two rows have the same value in a column. Email addresses, usernames, employee IDs should be UNIQUE. Unlike PRIMARY KEY, UNIQUE allows NULL (multiple NULLs are allowed because NULL ≠ NULL). You can have multiple UNIQUE columns in one table.
In layman terms: UNIQUE is like no two people can have the exact same email address in a system. It prevents duplicates but allows “no email” (NULL) for multiple users.
CHECK Constraint: Valid Values Only
CHECK constraints validate data. CHECK (age >= 18) prevents storing negative ages or ages under 18. CHECK (amount > 0) prevents negative amounts. CHECK (status IN (active, inactive)) restricts status to valid values. CHECK is enforced at the database level, independent of application logic.
In layman terms: CHECK is like a bouncer at a club. Only people 18+ enter. If you try to bring a 16-year-old, the bouncer (CHECK constraint) stops you at the door.
Cascading Actions: ON DELETE CASCADE
FOREIGN KEY constraints can define what happens when a parent record is deleted. ON DELETE CASCADE automatically deletes child records. ON DELETE RESTRICT prevents deletion if children exist. ON DELETE SET NULL sets child foreign keys to NULL. Choose carefully: CASCADE is convenient but risky; RESTRICT forces explicit cleanup.
5 Complete Interview Questions with Solutions
Question 1: PRIMARY KEY vs UNIQUE
A Users table needs email to be unique but also needs a numeric ID as the primary key. How do you enforce both constraints and why is this better than using email as the primary key?
-- Correct: Surrogate PK + UNIQUE constraint CREATE TABLE users ( user_id INT PRIMARY KEY AUTO_INCREMENT, email VARCHAR(255) UNIQUE NOT NULL, name VARCHAR(100), created_at TIMESTAMP ); -- Why this is better: -- user_id: stable, short, never changes -- UNIQUE email: prevents duplicates, allows fast email lookups -- Foreign keys use user_id (short), not email (long) -- Bad: Email as PRIMARY KEY CREATE TABLE users ( email VARCHAR(255) PRIMARY KEY, ... ); -- Problem: Email can change, user wants to use new email -- Foreign keys must reference email (long strings) -- Slow joins on string columns
Explanation: PRIMARY KEY should be stable and immutable. Emails change (job change, domain shutdown). Use auto-increment ID as PK, add UNIQUE constraint on email. This separates the concerns: PK for internal identification, UNIQUE for preventing duplicates. Constraints fundamentals: surrogate keys + natural key constraints = best practice.
Google – Senior Database EngineerAmazon – Senior DBAMicrosoft – Data Architect
Question 2: FOREIGN KEY Integrity
An order references a customer that no longer exists. How do FOREIGN KEY constraints prevent this, and what happens if you try to insert an order for a non-existent customer?
CREATE TABLE customers ( customer_id INT PRIMARY KEY, name VARCHAR(100) ); CREATE TABLE orders ( order_id INT PRIMARY KEY, customer_id INT, amount DECIMAL(10,2), FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ); -- Try to insert order for non-existent customer INSERT INTO orders VALUES (1, 999, 100); -- Error: Foreign key constraint fails -- customer_id 999 does not exist in customers table -- Solution: Insert customer first INSERT INTO customers VALUES (999, John); INSERT INTO orders VALUES (1, 999, 100); -- Success: Now customer exists
Explanation: FOREIGN KEY validates that referenced data exists. Prevents orphaned records. Constraints fundamentals: referential integrity ensures data consistency across tables. Without FK constraints, you can create orders for non-existent customers, creating inconsistencies.
Meta – Senior Database EngineerOracle – Data ArchitectIBM – Senior DBA
Question 3: CHECK Constraint for Data Validation
An employee salary must be positive (> 0) and not exceed $1,000,000. How do you enforce these business rules using CHECK constraints instead of application code?
CREATE TABLE employees ( employee_id INT PRIMARY KEY, name VARCHAR(100), salary DECIMAL(10,2), CHECK (salary > 0), CHECK (salary <= 1000000) ); -- Or combined CHECK CREATE TABLE employees ( employee_id INT PRIMARY KEY, name VARCHAR(100), salary DECIMAL(10,2), CHECK (salary > 0 AND salary <= 1000000) ); -- Try to insert invalid salary INSERT INTO employees VALUES (1, John, -50000); -- Error: CHECK constraint fails INSERT INTO employees VALUES (1, John, 50000); -- Success: Salary valid
Explanation: CHECK constraints validate data at the database level. Even if application has bugs, the database prevents invalid data. Constraints fundamentals: never trust application validation alone. CHECK is cheaper than application validation and protects against all entry points.
Stripe – Senior Data EngineerLinkedIn – Database Architect
Question 4: Cascading Deletes
If you delete a customer, should all their orders be automatically deleted? When would you use ON DELETE CASCADE vs ON DELETE RESTRICT?
-- ON DELETE CASCADE: Orders auto-delete with customer
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
ON DELETE CASCADE
);
-- Delete customer 1: All their orders gone
DELETE FROM customers WHERE customer_id = 1;
-- Result: Customer deleted, orders deleted
-- ON DELETE RESTRICT: Prevent deletion if orders exist
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
ON DELETE RESTRICT
);
-- Delete customer 1: Error if orders exist
DELETE FROM customers WHERE customer_id = 1;
-- Error: Cannot delete, orders reference this customer
-- When to use CASCADE:
-- - Blog comments delete when blog deleted (safe)
-- When to use RESTRICT:
-- - Orders stay even if customer deleted (audit trail)
-- - Prevents accidental bulk deletion
Explanation: CASCADE is risky—one delete cascades and destroys related data. RESTRICT forces explicit cleanup, preventing accidents. Constraints fundamentals: choose carefully. For audit trails, keep deleted customer records and mark as deleted; dont cascade delete.
Google – Senior Data ArchitectAmazon – Database Engineer
Question 5: Circular FOREIGN KEY Dependencies
Tables A and B have FOREIGN KEY constraints pointing to each other (A→B and B→A). How do you insert rows when both depend on each other, and what design problem does this reveal?
-- Problem: Circular dependency CREATE TABLE a ( a_id INT PRIMARY KEY, b_id INT REFERENCES b(b_id) ); CREATE TABLE b ( b_id INT PRIMARY KEY, a_id INT REFERENCES a(a_id) ); -- Try to insert: Fails! -- INSERT INTO a: Need b to exist first -- INSERT INTO b: Need a to exist first -- Deadlock: Neither can be inserted -- Solution 1: Defer constraint checking BEGIN TRANSACTION; SET CONSTRAINTS ALL DEFERRED; INSERT INTO a VALUES (1, 1); INSERT INTO b VALUES (1, 1); COMMIT; -- Both succeed at commit time -- Solution 2: Redesign -- This design is wrong. Review the relationship. -- Usually circular FKs indicate a many-to-many relationship. -- Use a junction table instead.
Explanation: Circular dependencies are design flaws. Constraints fundamentals: prevent them during design. If you encounter this, likely the data model is wrong. Use deferred constraints as a workaround, but redesign properly using junction tables for many-to-many relationships.
Microsoft – Senior Database ArchitectMeta – Data Engineer
15 Practice Questions (Test Yourself)
Question 6: When you enable a FOREIGN KEY constraint on a column that already contains invalid references to non-existent parent rows, what error would occur, and how would you clean up the data before enabling the constraint?
Google – Senior Data EngineerAmazon – Senior DBA
Question 7: If you have a CHECK constraint that enforces business logic like status must be active or inactive, what happens when business rules change and a new status value is allowed, and how would you update the constraint safely in production?
Microsoft – Senior Database EngineerMeta – Data Architect
Question 8: When a UNIQUE constraint allows NULL values and multiple rows have NULL in that column, how does the database handle "uniqueness" of NULL, and what are the implications for data integrity?
Oracle – Senior Data ArchitectIBM – Database EngineerLinkedIn – Senior DBA
Question 9: If you have a composite UNIQUE constraint on columns (customer_id, product_id), how does the database determine uniqueness, and what if one of those columns contains NULL?
Stripe – Senior Data EngineerGoogle – Database Architect
Question 10: When you have a self-referencing FOREIGN KEY like an employees manager_id pointing to another employee in the same table, what constraints would you need to prevent circular hierarchies?
Amazon – Senior Data ArchitectMicrosoft – Database EngineerMeta – Senior DBA
Question 11: If you need to temporarily disable FOREIGN KEY constraints to perform bulk operations like data migration or cleanup, what are the risks, and how would you re-enable them safely?
Flipkart – Senior Database EngineerLinkedIn – Data Architect
Question 12: When multiple tables have FOREIGN KEY constraints on the same parent table, how do you delete a parent row if ON DELETE RESTRICT is set on all children, and what alternative strategies exist?
Google – Senior Data ArchitectOracle – Senior DBAIBM – Data Architect
Question 13: If you have both application-level validation and database constraints enforcing the same rule, is this redundant, or does each serve a distinct purpose in your data integrity strategy?
Stripe – Database EngineerAmazon – Senior Data Engineer
Question 14: If a CHECK constraint depends on values in other tables like salary must not exceed department budget, how would you implement this complex constraint, and what are the limitations of simple CHECK constraints?
Microsoft – Senior Data ArchitectMeta – Database EngineerGoogle – BI Architect
Question 15: When you have ON DELETE SET NULL on a FOREIGN KEY, what happens to rows with NULL values, and how might this create unintended data loss or inconsistency?
LinkedIn – Senior Data EngineerFlipkart – Data Architect
Question 16: How would you audit which constraints are defined on your database tables, and what tools or queries would help identify missing constraints that should be added for data integrity?
Stripe – Senior Data ArchitectAmazon – Database EngineerOracle – Senior DBA
Question 17: If you need to add a NOT NULL constraint to an existing column that contains NULL values in production, what migration strategy would you use without losing data or blocking the application?
Google – Senior Data EngineerIBM – Data Architect
Question 18: When DEFAULT values conflict with CHECK constraints, how do you ensure that newly inserted rows without explicit values satisfy the CHECK requirement?
Meta – Senior Data ArchitectMicrosoft – Database EngineerLinkedIn – Senior BI Engineer
Question 19: If you have partial uniqueness requirements like email must be unique only for active users, not for deleted users, how would you implement this using constraints or alternative approaches?
Stripe – Database EngineerAmazon – Senior Data Architect
Question 20: How would you design and test a comprehensive set of constraints to ensure that a complex database schema maintains data integrity across all tables and relationships?
Google – Senior Database ArchitectOracle – Senior DBAIBM – Data Architect
Ready to Master Database Constraints?
These 20 practice questions are just the tip of the iceberg. Get our comprehensive Data Analytics: Ace All The Interview Concepts course.