[BUG]: issue with Durable Object migrations and foreign keys

### Report hasn't been filed before.

- [x] I have verified that the bug I'm about to report hasn't been filed before.

### What version of `drizzle-orm` are you using?

^0.40.0

### What version of `drizzle-kit` are you using?

^0.30.5

### Other packages

_No response_

### Describe the Bug

## Description

I've encountered a serious bug in Drizzle ORM's Durable Object migration system when dealing with tables that have foreign key relationships, particularly with self-referencing tables and multiple dependent tables.

## Environment

- Drizzle ORM with Cloudflare Durable Objects
- Tables with foreign key relationships (including self-referencing foreign keys)

## Issue

When attempting to migrate a schema with complex foreign key relationships, the migration fails with a rollback error that doesn't provide helpful information about the actual problem. After investigation, I found two critical issues:

1. When creating a migration for a table that has a self-reference (foreign key to itself for parent objects) and other tables referencing it, the migration temp table's foreign key incorrectly points to the old table instead of the new temp table.

2. The migration cannot drop the new table because other tables (like comments) and the new migration temp table have objects that point to the old table, creating a circular dependency that prevents the migration from completing.

## Steps to Reproduce

1. Create a schema with at least two tables:
- A "scene_objects" table with a self-referencing foreign key for parent objects
- A "comments" table with foreign keys to the scene_objects table

2. Make a change to the scene_objects table schema

3. Attempt to run a migration

## Error Observed

The migration fails with a generic "rollback error" that doesn't provide details about the actual problem. The real error is hidden because when `tx.rollback()` happens, it throws an exception that prevents the actual error from being thrown.

## Workaround

I had to:
1. Revert drizzle meta/migrations to before this problem occurred
2. Remove all `.references` in the table schema to eliminate foreign keys
3. Create another migration
4. Continue without foreign keys until the issue is fixed

## Debugging Process

I had to create a custom migration file to see the actual error:

```typescript
} catch (error: unknown) {
const e = error instanceof Error ? error : new Error(String(error));
console.error("[SceneDurableObjectSQL] Database migration failed:", {
error: e,
errorMessage: e.message,
errorStack: e.stack,
migrations: Object.keys(migrations),
});
tx.rollback();
throw error;
}
```

This helped me see that the issue was related to foreign key constraints during the migration process.

## Suggested Fix

1. Improve error reporting: Allow users to see the real error, perhaps with a callback or better error handling that preserves the original error details.

2. Modify the migration strategy for tables with foreign key relationships:
- When creating a migration, identify tables that have FKs on the changed table
- Temporarily recreate them without the FK constraints
- For self-referencing tables, handle them specially
- Update the table with a rename from temp to original
- Add the FKs back to the dependent tables

3. Consider a recursive approach, since if a dependent table changes and something else depends on it, the same problem will occur.

## Additional Context

This issue is particularly problematic because the error messages are not helpful, making it extremely difficult to diagnose without deep knowledge of Drizzle's internals. Thankfully, since Drizzle is open source, I was able to create a custom migration handler to see what was happening, but this wouldn't be possible for most users.

Thank you for your attention to this issue. Let me know if you need any additional information or clarification.