SQL Learning Hub
SQL UNIQUE Constraint
SQL UNIQUE Constraint
Use the UNIQUE constraint to prevent duplicate values in columns and protect data integrity in your database.
What Is the SQL UNIQUE Constraint?
The UNIQUE constraint ensures that all values in a column—or a combination of columns—are different from each other. It's similar to a PRIMARY KEY, but with two key differences: a table can have multiple UNIQUE constraints, and UNIQUE columns can contain NULL values (depending on the SQL dialect).
Key Properties
- Prevents duplicate values in specified columns
- Can be applied to single columns or combinations of columns
- Multiple UNIQUE constraints allowed per table
- Can contain NULL values (unlike PRIMARY KEY)
Using UNIQUE in Table Creation
Column-Level Definition
Define unique constraints directly on columns:
Table-Level Definition
Define unique constraints separately from column definitions:
Composite UNIQUE Constraint
Enforce uniqueness across multiple columns:
Adding UNIQUE Constraints to Existing Tables
You can add unique constraints to existing tables using ALTER TABLE:
UNIQUE vs. PRIMARY KEY
Key Differences:
| Feature | UNIQUE | PRIMARY KEY |
|---|---|---|
| NULL values | Allowed (usually) | Not allowed |
| Multiple per table | Yes | No (only one) |
| Auto-indexed | Yes | Yes |
| Typical use | Business rules | Row identity |
Best Practices
Tips for Using UNIQUE Constraints
- Use for natural keys like usernames, email addresses, or product codes
- Consider case sensitivity in your database when using UNIQUE on text fields
- Keep composite unique constraints minimal - fewer columns are better
- Be aware of NULL handling differences across database systems
- Use meaningful constraint names for easier maintenance
Common Interview Questions
Q: How does NULL affect UNIQUE constraints?
Most SQL databases allow multiple NULL values in a UNIQUE column because NULL represents 'unknown' and two unknown values are not considered equal. However, some databases (like Microsoft SQL Server) only allow one NULL in a UNIQUE column.
Q: When would you use a composite UNIQUE constraint?
In our schema, we use a composite UNIQUE constraint on interactions (user_id, video_id, interaction_type) to ensure a user can only have one of each type of interaction per video. This prevents duplicate likes, comments, or other interactions while still allowing different types of interactions.
Ready for hands-on SQL practice?
We have 200+ questions on real companies and real products.
Related Topics
