sql / intermediate
Snippet
Enforcing Uniqueness Across Multiple Columns with Unique Indexes
A multi-column composite unique index prevents duplicate combinations of values across table columns while simultaneously improving search performance for queries filtering on those leading columns.
snippet.sql
sql
1
2
CREATE UNIQUE INDEX idx_user_role_assignmentON user_roles (user_id, role_id);
Breakdown
1
CREATE UNIQUE INDEX idx_user_role_assignment
Defines a new unique index named idx_user_role_assignment in the database schema.
2
ON user_roles (user_id, role_id);
Applies the uniqueness constraint on the combined user_id and role_id columns of the user_roles table.