Working with Unique Constraints
1. Creating Single Field Unique
| Aspect | Detail |
|---|
| Index | Unique B-tree |
| Lookup | Usable in findUnique |
2. Defining Composite Unique
@@unique([provider, providerAccountId])
| Pattern | Use |
|---|
| OAuth accounts | (provider, providerAccountId) |
| Memberships | (userId, orgId) |
3. Using Unique in findUnique
await prisma.account.findUnique({
where: { provider_providerAccountId: { provider: "github", providerAccountId: "42" } }
});
| Aspect | Detail |
|---|
| Compound key | Auto-named or via name |
| Custom name | @@unique([..], name: "providerKey") |
4. Handling Unique Violations
| Code | Detail |
|---|
P2002 | e.meta.target lists offending fields |
| Recover | Convert to upsert or surface 409 |
5. Creating Conditional Unique Constraints
| DB | Approach |
|---|
| PG | Partial index via raw SQL: CREATE UNIQUE INDEX ... WHERE deletedAt IS NULL |
| MySQL | Filtered uniqueness emulated via expression columns |
6. Using Case-Insensitive Unique
| DB | Approach |
|---|
| PG | String @db.Citext @unique |
| MySQL | Use utf8mb4_0900_ai_ci collation |
7. Implementing Partial Unique Indexes
| Use Case | Detail |
|---|
| Soft delete | Unique only when not deleted |
| Active flag | Unique only for active rows |
| Implementation | Raw SQL in migration |
8. Updating Unique Fields
| Risk | Mitigation |
|---|
| Collision | Wrap in try/catch for P2002 |
| Race | Use transaction with serializable isolation |
9. Deleting by Unique Fields
await prisma.user.delete({ where: { email: "a@b.io" } });
| Aspect | Detail |
|---|
| Constraint | Must be unique field |
| Composite | Use compound shape |
10. Validating Uniqueness at Runtime
| Approach | Detail |
|---|
| Pre-check + insert | Vulnerable to race conditions |
| Try insert + handle P2002 | Recommended pattern |
| Upsert | Race-safe for idempotent ops |