Which CHECK constraint correctly prevents invalid user status values?

The IN operator with lowercase enum values is the standard, readable pattern. Option 0 doesn't include 'banned'. Option 2 is too permissive. Option 3 creates case-conversion issues.

What happens if you try to insert a row with status = 'ACTIVE' (uppercase) into a table with CHECK (status IN ('active', 'inactive', 'banned'))?

String comparisons in SQL are case-sensitive. 'ACTIVE' ≠ 'active', so the CHECK condition is false and the insert is rejected with a constraint violation.

Which of these is a valid use case for a cross-column CHECK constraint?

Cross-column checks compare two columns logically. Option 1 mixes independent fields. Option 2 is the classic temporal constraint. Option 3 is string validation (wrong tool). Option 4 uses aggregation (not allowed in CHECK).

A developer runs an overnight migration that inserts 100,000 users with status = 'verified' instead of the enum values. What prevents this with a CHECK constraint?

The database constraint blocks each invalid insert. The migration fails early and prevents bad data from entering. The other options require external systems and are not guaranteed.

You need to enforce that a priority column is a number between 1 and 5 inclusive. Which CHECK is most efficient?

BETWEEN is the standard, most readable way to express inclusive numeric ranges. All are functionally correct, but BETWEEN is idiomatic SQL.

In an enrollments table with PRIMARY KEY (student_id, course_id), what does this constraint prevent?

The composite PK ensures the combination (student_id, course_id) is unique. One student cannot enroll in the same course twice, but can enroll in different courses.

A notification_preferences table has PRIMARY KEY (user_id, notification_type). Can two rows exist with user_id=1 and different notification_types?

Composite PK enforces uniqueness on the combination. (1, 'mention') ≠ (1, 'like'), so both rows can coexist for the same user_id.

Why might you use a surrogate key (SERIAL PRIMARY KEY) instead of a composite primary key when designing a post_tags table?

Foreign keys typically reference a single column. A surrogate key simplifies FK references from other tables, even though you still need UNIQUE (post_id, tag_id) to enforce the business rule.

In a daily_metrics table with PRIMARY KEY (entity_id, metric_date), can you insert two rows with the same entity_id if they have different dates?

Composite PK enforces uniqueness on (entity_id, metric_date). Different dates create different combinations, so multiple rows per entity_id are allowed.

A follows table has UNIQUE (follower_id, followee_id). What does this allow?

The UNIQUE constraint only prevents duplicate (follower_id, followee_id) pairs. (1,2) and (2,1) are different. Multiple users can follow the same target. Timestamp doesn't make the pair unique.

In a password_rules table with UNIQUE (org_id, rule_name), what is prevented?

The UNIQUE constraint prevents duplicate (org_id, rule_name) pairs. Org 1 cannot have two min_length rules, but Org 1 and Org 2 can each have their own.

What is the difference between PRIMARY KEY and UNIQUE regarding NULL values?

PRIMARY KEY columns cannot be NULL by definition. UNIQUE columns can be NULL, and most databases allow multiple NULL values (NULL ≠ NULL in SQL).

You use CONSTRAINT uq_follow_pair UNIQUE (follower_id, followee_id). What benefit does naming the constraint provide?

Named constraints appear in error messages like 'violates unique constraint uq_follow_pair', making debugging easier. Named constraints don't change enforcement or performance.

A web application validates user status before INSERT, but a data migration script bypasses this validation. Which defense catches invalid statuses in the migration?

Only the database constraint guarantees enforcement across all entry points (app, migration, direct SQL, etc.). The other layers only protect the web app code path.

Your application refactored from Python to Go. The new Go app has a bug that doesn't validate order status. What saves you?

Database constraints are language-agnostic and survive application rewrites. They persist through refactors, language changes, and framework swaps.

Which scenario BEST demonstrates why database constraints are the 'last line of defense'?

Third-party systems bypass your application logic entirely. The database constraint is the only guarantee that prevents invalid data, regardless of the insertion method.

In a notifications table, what does CHECK (read_at IS NULL OR read_at >= created_at) prevent?

The constraint enforces temporal logic: a notification cannot be marked read (read_at set) at an earlier time than it was created. This prevents logical impossibilities.

A notification_preferences table has PRIMARY KEY (user_id, channel, notification_type). What does this enforce?

The composite PK ensures uniqueness on the 3-column combination. User 1 can have (email, mention) and (sms, mention) as separate rows.

The notification_delivery_log has UNIQUE (notification_id, channel, attempt_number). Why is this important?

The constraint prevents duplicate log entries for the same (notification, channel, retry attempt), ensuring idempotent tracking. It doesn't limit total attempts or guarantee delivery.

What does the CHECK constraint in notification_preferences that validates quiet hours accomplish?

The constraint CHECK ((quiet_hours_start IS NULL AND quiet_hours_end IS NULL) OR (quiet_hours_start IS NOT NULL AND quiet_hours_end IS NOT NULL)) prevents partial specification of quiet hours.

In the password_policies table with UNIQUE (org_id, rule_name), what happens if Org 1 tries to store two different min_length rules?

The UNIQUE constraint on (org_id, rule_name) prevents inserting a second row with Org 1 and min_length. The database rejects the second insert.

The user_sessions table has CHECK (expires_at > created_at). What scenario does this prevent?

The constraint enforces temporal validity: a session's expiry must be a future timestamp relative to creation. It prevents logical impossibilities.

Why does the password_history table have an algorithm column with a CHECK constraint?

The CHECK (algorithm IN ('bcrypt', 'argon2', 'scrypt')) documents which algorithms are used and future-proofs the schema for adding stronger algorithms without schema changes.

In login_attempts, what does CHECK (result IN ('success', 'wrong_password', ...)) provide?

The constraint ensures login_attempts records only valid outcome values, maintaining data integrity. Detection and locking must be implemented in application logic.

In applications with UNIQUE (applicant_id, job_posting_id), what is prevented?

The UNIQUE constraint on (applicant_id, job_posting_id) ensures each applicant can apply to a job only once. Multiple applicants to the same job is allowed.

The job_postings table has CHECK (salary_min IS NULL OR salary_max IS NULL OR salary_min <= salary_max). What is allowed?

The constraint allows partial salary specs (one NULL, one value) or both NULL. It only enforces min <= max when both are present. Option 1 violates the constraint.

Interviews table has UNIQUE (application_id, round_number). What does this prevent?

The UNIQUE constraint prevents duplicate round_numbers for the same application. An application can have round 1, round 2, etc., but not two round 1 interviews.

Why does the applications table have both a stage column with CHECK and last_updated with CHECK (last_updated >= applied_at)?

The stage CHECK validates enum values. The last_updated CHECK enforces temporal logic (cannot be marked updated before applied). Two independent constraints for two different concerns.

If a job posting has deadline before posted_at due to a data bug, what prevents it from being inserted?

The database CHECK constraint enforces deadline >= posted_at, preventing illogical data regardless of how it's inserted (app, migration, direct SQL, third-party).

In the company size column with CHECK (size IN ('startup', 'small', ...)), what would happen if a legacy import sets size = 'very_small'?

The CHECK constraint explicitly enumerates valid values. 'very_small' is not in the list, so the database rejects the INSERT with a constraint violation error.

🍪 TechCookies

Free

📚Database Constraints as Data GuardsFree

9 sections

~30 min total

30 quick quizzes

4 SD challenges linked

0 of 9 done·~32 min left

Concepts›Database Constraints as Data Guards›What is Database Constraints as Data Guards?

0 / 9

9 sections~30 min

What is Database Constraints as Data Guards?

Database constraints enforce data integrity rules that survive application bugs and multiple entry points.

Read

~4 min

⋯

CHECK Constraints for Enum-Like Columns

CHECK constraints limit column values to specific allowed conditions, preventing invalid enum-like data entry.

ReadQuizCode

~4 min

⋯

Composite Primary Keys

Composite primary keys use multiple columns together to uniquely identify rows in many-to-many relationships.

ReadQuizCode

~4 min

⋯

UNIQUE Constraints on Multi-Column Combinations

Multi-column UNIQUE constraints prevent duplicate combinations of values across specified columns.

ReadQuizCode

~4 min

⋯

Why DB-Level Constraints Outlast Application Bugs

Database constraints protect data across all entry points and survive application refactors and logic errors.

ReadQuizCode

~4 min

⋯

System Design: Notification System

Notification systems use CHECK constraints on status, priority, and delivery channels with composite keys for preferences.

ReadQuizCode

~4 min

⋯

System Design: Password Validation

Password validation schemas enforce policies, history, and login attempts using CHECK and UNIQUE constraints.

ReadQuizCode

~4 min

⋯

System Design: Job Application Tracker

Job application trackers enforce application stages, interview rounds, and salary ranges with comprehensive constraints.

ReadQuizCode

~4 min

⋯

Practice test

30 questions

~10 min

Section 1 of 9Read

What is Database Constraints as Data Guards?

Database constraints enforce data integrity rules that survive application bugs and multiple entry points.

~4 min read

Constraints as Data Guards

Databases are not just storage boxes — they are the last line of defense for your data. Imagine a form on a website that accepts a user's role. Your application might validate that the role must be either "admin", "editor", or "viewer" — but what if someone bypasses the application and writes directly to the database? What if there is a bug in your code? This is where database-level constraints come in. They act as permanent guards that protect your data no matter how it gets written. In these notes, we will explore the most important constraint types, understand why they matter more than application-level validation, and apply them to real-world system design problems.

Mark this section complete.

Notes

🔍

Loading…