DEV Community

Jayson Gellido
Jayson Gellido

Posted on

Migrating Django from MySQL to Oracle: Handling Existing Tables and Many-to-Many Relationships

#django #oracle #adw #python

Migrating a Django project from MySQL to Oracle can be tricky, especially when your Oracle database already has existing tables with data. In this post, I’ll share a real-world problem I faced and how I solved it, so other developers can avoid similar headaches.

The Problem

While migrating a Django project from MySQL to Oracle, I ran into two major issues:

ORA-00955: name is already used by an existing object
This happens when Django tries to create a table that already exists in Oracle. Unlike MySQL, Oracle is case-sensitive when using quoted identifiers.

ORA-00942: table or view does not exist
This occurs when Django expects a table to exist with a certain name, but Oracle either doesn’t have it or Django’s default naming (lowercase, no quotes) doesn’t match Oracle’s actual table name (usually uppercase).

Many-to-Many Fields in Oracle

For example, I had a Profile model with several ManyToManyFields:

class Profile(models.Model):
    account_assignment = models.ManyToManyField(AccountModel, blank=True)
    bypass_po_box_restriction = models.ManyToManyField(AccountModel, blank=True)
Enter fullscreen mode Exit fullscreen mode

After migrating to Oracle, Django couldn’t find the join tables, giving the ORA-00942 error.

Oracle automatically converts unquoted table names to uppercase, so the join tables were actually named something like:

PROFILE_PROFILE_ACCOUNT_ASSIGNMENT
PROFILE_PROFILE_BYPASS_PO_BOX_RESTRICTION

The Fix

There are two key steps:

  1. Use db_table for Many-to-Many fields

You can tell Django to use the exact table name that exists in Oracle:

class Profile(models.Model):
    account_assignment = models.ManyToManyField(
        AccountModel,
        blank=True,
        db_table='"PROFILE_PROFILE_ACCOUNT_ASSIGNMENT"'
    )
    bypass_po_box_restriction = models.ManyToManyField(
        AccountModel,
        blank=True,
        db_table='"PROFILE_PROFILE_BYPASS_PO_BOX_RESTRICTION"'
    )
Enter fullscreen mode Exit fullscreen mode

Note the double quotes "...". Oracle treats quoted identifiers as case-sensitive, so this ensures Django uses the correct table.

  1. Use db_table in the model Meta for normal tables

For other models that already exist in Oracle, like:

MAIN_SHIPPINGCARRIERSERVICESMODEL

You can specify the table name directly in Meta:

class ShippingCarrierServicesModel(models.Model):
    # fields...

    class Meta:
        db_table = 'MAIN_SHIPPINGCARRIERSERVICESMODEL'
Enter fullscreen mode Exit fullscreen mode

If the table was created without quotes, you don’t need extra quotes in db_table.

Key Takeaways

Oracle is case-sensitive with quoted identifiers, so always check how your tables were created.

Use db_table in Django models to point to existing tables, avoiding unnecessary migrations or table creation errors.

For many-to-many join tables, db_table is crucial if you’re working with an existing schema.

Always check the actual table names in Oracle:

SELECT table_name FROM user_tables;
Enter fullscreen mode Exit fullscreen mode

Conclusion

Migrating Django projects from MySQL to Oracle is not just a matter of changing the database driver. Table naming conventions, many-to-many relationships, and Oracle’s case sensitivity can create unexpected errors.

Using db_table strategically allows you to work with existing Oracle tables without rewriting your entire schema or losing data.

Top comments (0)