sql / expert
Snippet
Optimizing Intersection Tables with Composite Primary Keys
Using a composite primary key ensures uniqueness in many-to-many relationships while providing a 'clustering' effect for queries filtered by the first column. Adding a reverse-order index optimizes queries filtered by the second column.
snippet.sql
1
2
3
4
5
6
7
CREATE TABLE user_permissions (user_id INTEGER NOT NULL,permission_id INTEGER NOT NULL,granted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,PRIMARY KEY (user_id, permission_id));CREATE INDEX idx_perm_user ON user_permissions (permission_id, user_id);
Breakdown
1
PRIMARY KEY (user_id, permission_id)
Enforces uniqueness and automatically creates a B-tree index starting with user_id.
2
CREATE INDEX idx_perm_user ... (permission_id, user_id)
Creates a covering index to allow fast lookups from the permission side.