How do I specify unique constraint for multiple columns in MySQL?

In MySQL, a unique constraint ensures that all values in a column—or a group of columns—are unique across the rows in a table. When you need to enforce uniqueness on multiple columns, you can do so either while creating the table or by altering an existing one. Below are examples and best practices to help you implement this efficiently.

1. Creating a New Table with a Multi-Column Unique Constraint

You can define a UNIQUE key or index on multiple columns in the CREATE TABLE statement:

CREATE TABLE your_table (
    id INT AUTO_INCREMENT PRIMARY KEY,
    column1 VARCHAR(100),
    column2 VARCHAR(100),
    column3 VARCHAR(50),
    UNIQUE KEY unique_col1_col2 (column1, column2)
);

• UNIQUE KEY unique_col1_col2 (column1, column2): This ensures that for every combination of column1 and column2, no two rows can share the same values.

Notes

1. Naming: Giving a descriptive name (unique_col1_col2) to your index is optional but helps identify it easily when troubleshooting or making schema changes.
2. Multiple UNIQUE Keys: A table can have several unique constraints on different column combinations.

2. Adding a Multi-Column Unique Constraint to an Existing Table

If your table is already created, use an ALTER TABLE statement to add the unique constraint:

ALTER TABLE your_table
ADD UNIQUE unique_col1_col2 (column1, column2);

Example

ALTER TABLE employees
ADD UNIQUE unique_firstname_lastname (first_name, last_name);

Here, first_name and last_name combined must be unique—no two employees can have the exact same first and last names.

3. Handling Duplicate Rows Before Adding a Unique Constraint

If there are existing rows that violate the uniqueness requirement, MySQL will fail to create the new unique constraint. You’ll need to either:

1. Update or Remove the duplicate rows.
2. Temporarily rename or drop the data that conflicts with your unique requirement.

For example:

DELETE FROM employees
WHERE first_name = 'John' 
  AND last_name = 'Doe'
  AND id <> 100;  -- Keep one valid record; remove the rest

4. Why Use a Multi-Column Unique Constraint?

1. Data Integrity: Ensures no two rows have the same multi-column combination, e.g., (country_code, phone_number), (user_id, role_id), (order_id, product_id), etc.
2. Enforces Business Logic: Aligns the database schema with real-world rules (e.g., an email address can’t be used by multiple users in the same domain).
3. Performance: Can speed up lookups on those columns if queries frequently filter by both at once, although you should also evaluate if a separate index might be needed.

5. Best Practices

• Use Descriptive Names: When naming your unique constraint, include the column names.
• Keep It Minimal: Don’t add redundant columns. Only the smallest set of columns that ensures uniqueness should be in the unique key.
• Avoid Over-Indexing: While unique constraints help with data integrity, too many indexes (unique or otherwise) can hurt write performance.
• Check Existing Data: Always handle or remove duplicates before adding a new unique constraint.

6. Dive Deeper into Database Design and SQL

If you’re looking to level up your database and SQL skills further—especially for interviews or advanced projects—consider the following courses from DesignGurus.io:

• Grokking SQL for Tech Interviews
  Sharpen your querying techniques, discover common performance pitfalls, and master real-world SQL problem-solving.

• Grokking Database Fundamentals for Tech Interviews
  Explore indexing strategies, normalization principles, and best practices for scalable database design.

7. Get Personalized Feedback via Mock Interviews

To prepare for critical job interviews where database knowledge is key, you can book Mock Interviews with ex-FAANG engineers at DesignGurus.io. You’ll receive real-time feedback, refine your communication, and learn how to tackle tricky SQL or system design questions.

Conclusion

Enforcing uniqueness across multiple columns in MySQL is straightforward with either the CREATE TABLE or ALTER TABLE statements. By applying a multi-column unique constraint, you ensure data integrity that matches real-world business logic. Just remember to address any existing duplicates first and monitor your indexing strategy for optimal performance.

Keep exploring advanced SQL patterns and techniques to build robust, scalable, and efficient databases. Good luck!