# UUID Migration Drop Before Rename Fix

## Issue: "column 'company_id' of relation 'companies' already exists"

### Problem
The error occurred because we were trying to rename [company_uuid](file:///d:/bookbeach/backend/app/models/company.py#L37-L37) to [company_id](file:///d:/bookbeach/backend/app/models/company.py#L37-L37) in the [companies](file:///d:/bookbeach/backend/app/models/company.py#L7-L43) table, but there was already a [company_id](file:///d:/bookbeach/backend/app/models/company.py#L37-L37) column in that table.

### Root Cause
The approach was incorrect because:
1. We were not dropping the old primary key columns before trying to rename the UUID columns
2. We were trying to rename a column to a name that already existed
3. The correct order should be:
   - Add UUID columns
   - Drop old primary key columns
   - Rename UUID columns to original names
   - Add new primary key constraints

### Solution
Fixed the order of operations by:

1. **Dropping old primary key columns** from key tables before renaming UUID columns
2. **Renaming UUID columns** to original names after dropping old columns
3. **Adding new primary key constraints** after renaming

### Key Changes Made

#### Before (Incorrect):
```sql
-- Section 5: Trying to rename without dropping old column first
ALTER TABLE companies RENAME COLUMN company_uuid TO company_id;
ALTER TABLE companies ADD PRIMARY KEY (company_id);
```

#### After (Correct):
```sql
-- Section 5: Dropping old column first, then renaming
ALTER TABLE companies DROP COLUMN company_id;
ALTER TABLE companies RENAME COLUMN company_uuid TO company_id;
ALTER TABLE companies ADD PRIMARY KEY (company_id);
```

### Files Updated
1. **[05_migrate_key_tables_to_uuid.sql](file://d:/bookbeach/database/scripts/05_migrate_key_tables_to_uuid.sql)** - Fixed the order of operations for dropping and renaming columns

### Verification
The migration script should now work correctly without the "column 'company_id' of relation 'companies' already exists" errors.