133 lines
3.4 KiB
Markdown
133 lines
3.4 KiB
Markdown
# Fix Customer Email Duplicate Error
|
|
|
|
## Problem
|
|
The unique index on `Customers.Email` was enforcing **global uniqueness** (across all companies), but in a multi-tenant system, different companies should be able to have customers with the same email address.
|
|
|
|
## Solution
|
|
Change the unique index to be scoped to `CompanyId`, allowing the same email across different companies while still preventing duplicates within the same company.
|
|
|
|
---
|
|
|
|
## Quick Fix (Run SQL Script)
|
|
|
|
### Step 1: Run the SQL Script
|
|
1. Open **SQL Server Management Studio**
|
|
2. Connect to your testing server
|
|
3. Open the file: **`fix-customer-email-index.sql`**
|
|
4. **Execute** the script
|
|
|
|
This will:
|
|
- ✅ Drop the old global unique index
|
|
- ✅ Create new company-scoped unique index
|
|
- ✅ Show verification that it worked
|
|
|
|
### Step 2: Test Seeding
|
|
1. Go to your web app: `/SeedData`
|
|
2. Click **"Seed Company Data"**
|
|
3. Should work perfectly now! ✨
|
|
|
|
---
|
|
|
|
## Alternative: Use EF Migration (For Production Deployment)
|
|
|
|
If you want to use EF migrations for a cleaner deployment:
|
|
|
|
### From Web Project Directory:
|
|
```bash
|
|
cd src/PowderCoating.Web
|
|
|
|
# Apply the migration
|
|
dotnet ef database update --project ../PowderCoating.Infrastructure
|
|
```
|
|
|
|
This will apply the migration: **`FixCustomerEmailIndexForMultiTenancy`**
|
|
|
|
---
|
|
|
|
## What Changed
|
|
|
|
### Before (Old Index):
|
|
```sql
|
|
CREATE UNIQUE INDEX IX_Customers_Email ON Customers (Email)
|
|
WHERE [Email] IS NOT NULL
|
|
```
|
|
❌ Problem: Only ONE customer across ALL companies can have `john.smith@acmemfg.com`
|
|
|
|
### After (New Index):
|
|
```sql
|
|
CREATE UNIQUE INDEX IX_Customers_Email ON Customers (CompanyId, Email)
|
|
WHERE [Email] IS NOT NULL AND [IsDeleted] = 0
|
|
```
|
|
✅ Solution: EACH company can have a customer with `john.smith@acmemfg.com`
|
|
|
|
---
|
|
|
|
## Examples
|
|
|
|
### Now This Works:
|
|
| CompanyId | Email | Status |
|
|
|-----------|-------|--------|
|
|
| 1 | john.smith@acmemfg.com | ✅ OK |
|
|
| 2 | john.smith@acmemfg.com | ✅ OK (different company) |
|
|
| 1 | jane.doe@example.com | ✅ OK |
|
|
|
|
### This Still Prevents Duplicates:
|
|
| CompanyId | Email | Status |
|
|
|-----------|-------|--------|
|
|
| 1 | john.smith@acmemfg.com | ✅ First insert OK |
|
|
| 1 | john.smith@acmemfg.com | ❌ DUPLICATE (same company) |
|
|
|
|
---
|
|
|
|
## Verification
|
|
|
|
After running the script, verify the index:
|
|
|
|
```sql
|
|
-- Check the new index definition
|
|
SELECT
|
|
i.name AS IndexName,
|
|
i.is_unique AS IsUnique,
|
|
STRING_AGG(COL_NAME(ic.object_id, ic.column_id), ', ') AS IndexColumns,
|
|
i.filter_definition AS Filter
|
|
FROM sys.indexes i
|
|
INNER JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
|
|
WHERE i.object_id = OBJECT_ID('Customers')
|
|
AND i.name = 'IX_Customers_Email'
|
|
GROUP BY i.name, i.is_unique, i.filter_definition
|
|
```
|
|
|
|
**Expected Result:**
|
|
- IndexName: `IX_Customers_Email`
|
|
- IsUnique: `1` (true)
|
|
- IndexColumns: `CompanyId, Email`
|
|
- Filter: `[Email] IS NOT NULL AND [IsDeleted] = 0`
|
|
|
|
---
|
|
|
|
## Build & Deploy
|
|
|
|
The migration is already in your code:
|
|
```
|
|
src/PowderCoating.Infrastructure/Migrations/
|
|
└─ 20260211160000_FixCustomerEmailIndexForMultiTenancy.cs
|
|
```
|
|
|
|
When you deploy to production:
|
|
```bash
|
|
dotnet ef database update --project ../PowderCoating.Infrastructure
|
|
```
|
|
|
|
Will automatically apply this migration.
|
|
|
|
---
|
|
|
|
## Summary
|
|
|
|
✅ **Index Fixed** - Scoped to CompanyId
|
|
✅ **Multi-Tenancy Safe** - Same email OK across companies
|
|
✅ **Duplicate Prevention** - Still blocks duplicates within a company
|
|
✅ **Soft Delete Aware** - Ignores deleted records
|
|
|
|
You're ready to seed! 🎉
|