Django - How to prevent database foreign key constraint creation

Question

I have a model that is backed by a database view.

class OrgCode(models.Model):
    org_code                = models.CharField(db_column=u'code',max_length=15) 
    org_description         = models.CharField(max_length=250)
    org_level_num           = models.IntegerField()

    class Meta:
        db_table = u'view_FSS_ORG_PROFILE'

I need to reference this in another model

class AssessmentLocation(models.Model):
    name                = models.CharField(max_length=150)
    org                 = models.ForeignKey(OrgCode)

I can't run syncdb because foreign key constraints cannot be created referencing a view.

 u"Foreign key 'FK__main_asse__org__1D114BD1' 
 references object 'view_FSS_ORG_PROFILE' 
 which is not a user table.", None, 0, -214
7217900), None)
Command:
CREATE TABLE [main_assessmentlocation] (
    [id] int IDENTITY (1, 1) NOT NULL PRIMARY KEY,
    [name] nvarchar(150) NOT NULL,
    [org] int NOT NULL REFERENCES [view_FSS_ORG_PROFILE] ([id]),
)

The workaround is to take out the Meta:db_table pointing to the view and let sync db create the the OrgCode table, then put the Meta:db_table back in after syncdb.

Is there a way to prevent the creation of foreign key constraints for certain models or fields?

Update: I added a static method to the related model indicating it's a view

class OrgCode(models.Model):
    org_code                = models.CharField(max_length=15)
    org_description         = models.CharField(max_length=250)

    @staticmethod
    def is_backend_view():
        return True

Then overrode DatabaseCreation.sql_for_inline_foreign_key_references in django_mssql creation.py:

def sql_for_inline_foreign_key_references(self, field, known_models, style):
    try: 
        field.rel.to.is_backend_view()
        return "", False
    except:
        return super(DatabaseCreation,self).sql_for_inline_foreign_key_references(field, known_models, style)

The generated sql from syncdb leaves out the constraint:

CREATE TABLE [main_assessmentlocation] (
    [id] int IDENTITY (1, 1) NOT NULL PRIMARY KEY,
    [name] nvarchar(150) NOT NULL,
    [org] int, -- NO FK CONSTRAINT ANYMORE --
);

It does involve hacking django_mssql so I'm going to keep on trying, maybe hooking into the django.db.backends.signals.connection_created signal will work...

score 21 · Accepted Answer · answered May 03 '13 at 13:29

21

django development version has a db_constraint field for ForeignKey model field - docs.

answered May 03 '13 at 13:29

iurii

2,597
22
25

Thanks! That's what I needed, now I'll just have to wait for it to land in a release and I can remove my hack. – Joe Petrini May 03 '13 at 15:42
1

`db_constraint ` can be safely used since version 1.6 – Ivor Zhou Dec 06 '16 at 03:48
I have done this for postgres and get an alter field entry in the migration file, but the database schema in postgres does not change at all. – Tim Richardson Feb 18 '21 at 00:06
@TimRichardson, I don't think that the DB schema needs to change in your case, as Django implements its constraints on the DB level. Instead, try searching for the interesting constraint in Postgres, e.g. as described here: https://dba.stackexchange.com/questions/214863/how-to-list-all-constraints-of-a-table-in-postgresql – iurii Feb 18 '21 at 10:24

score 4 · Answer 2 · answered Nov 09 '11 at 00:42

If you set managed=False (Django docs) in your model's Meta class, Django will not create the table when you run syncdb.

class AssessmentLocation(models.Model):
    name = models.CharField(max_length=150)
    org  = models.ForeignKey(OrgCode)

    class Meta:
        managed = False

Django has a hook to provide initial sql data. We can (ab?)use this to get Django to create the table immediately after running syncdb.

Create a file myapp/sql/assessmentlocation.sql, containing the create table statement:

CREATE TABLE [main_assessmentlocation] (
    [id] int IDENTITY (1, 1) NOT NULL PRIMARY KEY,
    [name] nvarchar(150) NOT NULL,
    [org] int, -- NO FK CONSTRAINT ANYMORE --
);

If you have other models with foreign keys to the AssessmentLocation model, you may have problems if Django tries to apply the foreign key constraint before executing the custom sql to create the table. Otherwise, I think this approach will work.

Thanks! I looked into this and it does work. The view based related object model is part of a framework I'm building to be used by other developers. I'm trying not to stray from syncdb model creation and the standard django docs. — Joe Petrini, Nov 09 '11 at 16:25

Django - How to prevent database foreign key constraint creation

2 Answers2

Linked