Enforce case-insensitive uniqueness (pattern approach)
SQL
Medium
7 views
Problem Description
In Users(email), you want emails to be unique even if case differs (A@x.com vs a@x.com). Write one practical approach using a stored generated column lower_email and a UNIQUE constraint on it.
Output Format
DDL statement(s)
Sample Test Case
Input:
Users(user_id, email)
Constraints
If your DB does not support generated columns, use triggers instead
Official Solution
ALTER TABLE Users ADD COLUMN lower_email VARCHAR(255); UPDATE Users SET lower_email = LOWER(email); ALTER TABLE Users ADD CONSTRAINT uq_lower_email UNIQUE (lower_email);
Solutions (0)
No solutions submitted yet. Be the first!
No comments yet. Start the discussion!