I am attempting to create a database model in Django 3.0 using django-mssql-backend as by db backend for SQL Server 2019. The database uses multiple schemas for the tables included in it with some of the tables being non-managed (already exist), and others being created from scratch through migrations. To get multiple schemas working, I used a hack I found on another answer on here that suggested formatting the tablename as follows:
class MyModel(models.Model):
...
class Meta:
db_table = 'schema].[table'
This is so that the SQL compiles to have the wrapped square brackets that automatically form on the outside complete the schema/table definition. The problem with this is that ForeignKey objects have their constraint names generate using this table name which causes invalid constraint names arise, causing the migration to fail once the tables are done being created and it comes time for the constraints to be created.\
They generate like this:
[schema1].[table1_colname_id_bc165567_fk2_schema2].[table_colname]
Is there a way to override this behaviour? If this can be overridden by forking the backend and adding manual compilation code how would I even go about doing that? Otherwise, how can I have foreign keys in my models while using multiple schemas and fully utilizing the ORM/migrations that come with Django?
I ended up figuring it out. After forking django-mssql-backend, I overrode the _create_index_name and _fk_constraint_name methods in the DatabaseSchemaEditor class within the sql_server.pyodbc.schema module, changing the table names to split('[')[-1], which omits the schema hack portion of the string and additional open bracket but shouldn't interfere with tables that don't have that hack applied as well.
Here's the overridden methods
(most of the code is identical to it's original implementation in django.db.backends.base.schema):
def _create_index_name(self, table_name, column_names, suffix=""):
"""
Generate a unique name for an index/unique constraint.
The name is divided into 3 parts: the table name, the column names,
and a unique digest and suffix.
"""
# CHANGE HERE (table_name to table_name.split('[')[-1]
_, table_name = split_identifier(table_name.split('[')[-1])
hash_suffix_part = '%s%s' % (names_digest(table_name, *column_names, length=8), suffix)
max_length = self.connection.ops.max_name_length() or 200
# If everything fits into max_length, use that name.
index_name = '%s_%s_%s' % (table_name, '_'.join(column_names), hash_suffix_part)
if len(index_name) <= max_length:
return index_name
# Shorten a long suffix.
if len(hash_suffix_part) > max_length / 3:
hash_suffix_part = hash_suffix_part[:max_length // 3]
other_length = (max_length - len(hash_suffix_part)) // 2 - 1
index_name = '%s_%s_%s' % (
table_name[:other_length],
'_'.join(column_names)[:other_length],
hash_suffix_part,
)
# Prepend D if needed to prevent the name from starting with an
# underscore or a number (not permitted on Oracle).
if index_name[0] == "_" or index_name[0].isdigit():
index_name = "D%s" % index_name[:-1]
return index_name
def _fk_constraint_name(self, model, field, suffix):
def create_fk_name(*args, **kwargs):
return self.quote_name(self._create_index_name(*args, **kwargs))
return ForeignKeyName(
model._meta.db_table.split('[')[-1],
[field.column],
# CHANGE HERE (db_table to db_table.split('[')[-1]
split_identifier(field.target_field.model._meta.db_table.split('[')[-1])[1],
[field.target_field.column],
suffix,
create_fk_name,
)
Hopefully this will be useful to anyone else that runs into a similar problem in the future if multiple schemas continue to be unsupported in Django's future versions.
EDIT: I have created a pull request on the django-mssql-backend repo to have these changes merged, so the context of this question will likely be a non-issue to people in the future if it gets approved.