1

I'm doing my first Django project and am wrestling with a legacy DB from a 3rd party program. The DB has a slightly unusual table structure for one section I'm trying to get to grips with.

The basic table layout is as follows:

PERSON
------
z_pk int primary key,
z_name text 

SPOUSEREL
---------
z_person int foreign_key(person.z_pk)
z_spouserel int

so for example, PERSON contains:

1, John Smith
2, Maggie Brown
3, Bob Johnson
4, Sarah Lee

And SPOUSEREL would contain the following:

1, 50
2, 50
1, 55
4, 55

This indicates that John Smith married Maggie Brown in "marriage number 50" and that John Smith also married Sarah Lee in "marriage number 55". The numbers 50 and 55 are numbers that simply create a link from one person to another to indicate a marriage and have no another relevance.

I've tried a combination of various ManyToManyField and ForeignKey, e.g.:

class Spouserel(models.Model):
    z_persons = models.ManyToManyField(Person, db_column='z_pk')
    z_spouserels = models.ManyToManyField("self")

but without success. From looking at the queries it's trying to execute, the lookup is failign with Django requiring an ID field but one is not present. I thought about using 'through' but in this case it would have to go through itself?

I'm not sure where to go on this.

famousgarkin
  • 13,687
  • 5
  • 58
  • 74
gmerrall
  • 390
  • 1
  • 8

1 Answers1

0

I think your models should be like these

class Person(models.Model):
    z_pk = models.AutoField(primary_key=True)
    z_name = models.TextField()

class Spouserel(models.Model):
    z_person = models.ForeignKey(Person, db_column='z_pk', primary_key=True)
    z_spouserel = models.IntegerField()

Note

The Spouserel model is more like a hack than a fix. Currently, the way Django works is that you would need to specify one of your model fields as a primary key. If it's unable to find such field, it will automatically add an id Autofield. To make your model work, you need to specify any field to be the primary key. It does not matter what field, Django just need to find primary_key=True. For more info, you can read this thread.

Rod Xavier
  • 3,983
  • 1
  • 29
  • 41
  • Thanks for your reply Rod. Unfortunately I can't make z_person a primary key due to the duplicated id's from the person table. Removing the primary_key parameter gets me back to the same problem of an id field missing e.g. `p = Zperson.objects.get(z_pk=41)` – gmerrall May 18 '14 at 10:52
  • As said in the link above, you can just choose any field and set `primary_key=True`. The field does not have to be unique. You just need to let Django know that you are setting that as the primary key so it won't add the id column. – Rod Xavier May 18 '14 at 10:55
  • As sorry I missed that. My old relational DB orimary key bias coming out. :) That's done the trick. Many thanks for your help. – gmerrall May 18 '14 at 11:10
  • No worries. But, keep in mind that the approach above is more like a hack and some Django features may not work. – Rod Xavier May 18 '14 at 11:12