CODEWITHSUNDEEP
djangodjango-models
Tim Richardson
Tim Richardson

Reputation: 7241

Foreign Key with db_constraint= False and postresql: foreign key appears to refer to wrong parent field

I am preparing tables for use in an analytics tool (Microsof Power BI). It would be helpful to show relationships between the tables to assist in report building. However, I don't want to use foreign keys with constraints because the method of data updating and integrity checking doesn't need constraints, and it will actually get in the way. Some of the alternate backends this code supports are for cloud databases that don't offer real foreign keys. I mention that because it means I am trying to define a foreignkey field like this:

 order_guid = models.ForeignKey("Dear_sales_header",to_field="order_guid",db_constraint=False,on_delete=models.DO_NOTHING)

The migration file has this:

  operations = [
        migrations.AlterField(
            model_name='sales_fact',
            name='order_guid',
            field=models.ForeignKey(db_constraint=False, on_delete=django.db.models.deletion.DO_NOTHING, to='dear_zoho_analytics.Dear_sales_header', to_field='order_guid'),
        ),
    ]

This table is routed to a different database

python manage.py migrate  --database=dear_analytics

does indicate that the migration file was applied (it is 0026)

  Applying dear_zoho_analytics.0026_auto_20210217_2245... OK

But when I inspect the postgresql schema in my IDE, the column in sales_fact is renamed to

order_guid_id

so it looks like I have done something wrong because this seems to reference the id field of the "parent table" dear_sales_header yet I need it to refer to dear_sales_header.order_guid which is unique but not the primary key.

Partial extract of Models:

class AnalyticsTable:
    # a dummy class used to mark the DearAnalytics tables. Creating a subclass of models.Model breaks things in the ORM such as bulk update
    pass


class Sales_fact(models.Model, AnalyticsTable):
    analytics_table_name = "sales_fact"
    #order_guid = models.CharField(max_length=1024, null=True, blank=True, help_text="")
    order_guid = models.ForeignKey("Dear_sales_header",to_field="order_guid",db_constraint=False,on_delete=models.DO_NOTHING)
    source_dear = models.CharField(max_length=1024, null=True, blank=True,
                                   help_text="Link to Dear instance which is the source of the data")


class Dear_sales_header(models.Model, AnalyticsTable):
    analytics_table_name = "dear_sales_header"
    source_dear = models.CharField(max_length=1024, null=True, blank=True,
                                   help_text="Link to Dear instance which is the source of the data")
    order_guid = models.CharField(max_length=1024, unique=True, help_text="")

    global_sale_status = models.CharField(max_length=1024, null=True, blank=True, help_text="SO Header status")
    order_status = models.CharField(max_length=1024, null=True, blank=True, help_text="")

Upvotes: 1

Views: 3558

Answers (1)

iklinac
iklinac

Reputation: 15738

Django adds suffix _id to ForeignKey name as documented

Behind the scenes, Django appends "_id" to the field name to create its database column name.

If you want name not to include any additions you should set db_column

The name of the database column to use for this field. If this isn’t given, Django will use the field’s name.

order_guid = models.ForeignKey(
    "Dear_sales_header", to_field="order_guid", db_constraint=False, 
    db_column='order_guid',on_delete=models.DO_NOTHING
)

In MRE I am not able to reproduce any undesired behavior, Django does properly map ForeignKey to column you set also when settingdb_constraint=True properly ads back right constraint and drops it as backward operation

    constraint testbench_sales_fact_order_guid_f2dab1c5_fk_testbench
        references testbench_dear_sales_header (order_guid)
        deferrable initially deferred

Upvotes: 2

Related Questions