Django - How to prevent database foreign key constraint creation

15.7k views Asked by At

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...

2

There are 2 answers

4
iurii On BEST ANSWER

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

1
Alasdair On

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.