Issue: Issues with CompositeKey
Author: coleifer
State: closed
Created: 2013-12-16 20:54:44

I have two models. Model A has a CompositeKey as primary_key, defined as indicated in [0]. Model B references model A with a ForeignKeyField(A) as indicated in [1]. Its seems that something is wrongly defined and raises an exception when tables are created in sqlite engine. 

Below is the full code reproducing the problem. 

Any help would be appreciated, Thanks in advance!

[0] http://peewee.readthedocs.org/en/latest/peewee/api.html#CompositeKey
[1] http://peewee.readthedocs.org/en/latest/peewee/api.html#ForeignKeyField

``` python
context_db = SqliteDatabase(":memory:")

class ContextModel(Model):
    class Meta:
    database = context_db

class A(ContextModel):
    id = IntegerField()
    another_thing = CharField()
    description = TextField()

    class Meta:   
        primary_key = CompositeKey('id','description')         

class B(ContextModel):
    fk = ForeignKeyField(A)

A.create_table()
B.create_table()
```

And the second invocation raises:

``` python
/.../python2.7/site-packages/peewee-2.1.6-py2.7.egg/peewee.pyc in get_db_field(self)
    709         to_pk = self.rel_model._meta.primary_key
    710         if not isinstance(to_pk, PrimaryKeyField):
--> 711             return to_pk.get_db_field()
    712         return super(ForeignKeyField, self).get_db_field()
    713 

AttributeError: 'CompositeKey' object has no attribute 'get_db_field'
```


So on the model `B` somewhere we would need to have columns mapped to `A.id` and `A.description`.  Then introduce a FK constraint and accessor that would do the right joins.


Does it make sense to add a `CompositeForeignKey`, so you'd end up with something like:

``` python

class A(ContextModel):
    id = IntegerField()
    another_thing = CharField()
    description = TextField()

    class Meta:   
        primary_key = CompositeKey('id', 'description')         

class B(ContextModel):
    a_id = IntegerField()
    a_description = TextField()
    a = CompositeForeignKey(A, 'a_id', 'a_description')
```


I think it does. If you think that's the best way to go. 
What's the difference between inspecting inside ForeignKey whether the target model's key is a CompositeKey against creating a new class for the CompositeForeignKey? 

But again, I think the fix you propose does the job pretty well. thanks!


> What's the difference between inspecting inside ForeignKey whether the target model's key is a CompositeKey against creating a new class for the CompositeForeignKey?

In a typical model the `ForeignKeyField` is a single column on a table (hence, it subclasses Field).  When you want to foreign-key to a model with a composite pk, you would need multiple columns (to mirror the columns making up the primary key).


What will happen in such case?

```
class A(ContextModel):
    id = IntegerField()
    another_thing = CharField()
    description = TextField()

    class Meta:
        indexes = (( 'a_id', 'a_description'), True) ,

class B(ContextModel):
    a_id = IntegerField()
    a_description = TextField()
    a = CompositeForeignKey(A, 'a_id', 'a_description')
```


@akaRem -- what do you mean?


I'm going to close this for now.


@coleifer Any progress on this ? It still crashes with the same exception.

If it's not peewee goal, it would be worth to mention it in docs, because 

> Peewee has very basic support for composite keys

Is not enough.


I think I'm running into this too, I want to have a composite foreign key like so:

``` python
# coding=utf-8
from peewee import *

db = SqliteDatabase('dump4.db')

class BaseModel(Model):

    class Meta:
        database = db

class table_A(BaseModel):
    tabA_id = IntegerField(primary_key=True, index=True)
    colA_data = IntegerField(index=True)


class table_C(BaseModel):
    tabA_id = ForeignKeyField(table_A, related_name='cs')
    tabC_id = IntegerField()
    colC_data = BooleanField()

    class Meta:
        primary_key = CompositeKey('tabA_id', 'tabC_id', 'colC_data')


class table_B(BaseModel):
    tabA_id = ForeignKeyField(table_A, related_name='bs')
    tabB_id = IntegerField()
    colB_data = BooleanField()

    class Meta:
        primary_key = CompositeKey('tabA_id', 'tabB_id', 'colB_data')

class table_D(BaseModel):
    tabA_id = ForeignKeyField(table_A, related_name='ds')
    tabD_id = IntegerField()
    tabB_id = ForeignKeyField(table_B, related_name='ds')
    colB_data = ForeignKeyField(table_B)
    colD_data = TextField()

    class Meta:
        primary_key = CompositeKey('tabA_id', 'tabD_id',
                                   'colB_data')


def main():
    tables = [
        table_A, table_B,
        table_C, table_D,
    ]
    db.connect()
    db.drop_tables(tables, safe=True)
    db.create_tables(tables, safe=True)
    #populate_db(db)
    print('DB done')


if __name__ == '__main__':
    main()
```

With peewee 2.4.7 on python 3.4.3 I get:

```
Traceback (most recent call last):
  File "test4.py", line 58, in <module>
    main()
  File "test4.py", line 52, in main
    db.create_tables(tables, safe=True)
  File "/Users/jimd/Desktop/p/sec/parse4/lib/python3.4/site-packages/peewee.py", line 2959, in create_tables
    create_model_tables(models, fail_silently=safe)
  File "/Users/jimd/Desktop/p/sec/parse4/lib/python3.4/site-packages/peewee.py", line 4049, in create_model_tables
    m.create_table(**create_table_kwargs)
  File "/Users/jimd/Desktop/p/sec/parse4/lib/python3.4/site-packages/peewee.py", line 3798, in create_table
    db.create_table(cls)
  File "/Users/jimd/Desktop/p/sec/parse4/lib/python3.4/site-packages/peewee.py", line 2956, in create_table
    return self.execute_sql(*qc.create_table(model_class, safe))
  File "/Users/jimd/Desktop/p/sec/parse4/lib/python3.4/site-packages/peewee.py", line 1666, in inner
    return self.parse_node(fn(*args, **kwargs))
  File "/Users/jimd/Desktop/p/sec/parse4/lib/python3.4/site-packages/peewee.py", line 1694, in _create_table
    columns.append(self.field_definition(field))
  File "/Users/jimd/Desktop/p/sec/parse4/lib/python3.4/site-packages/peewee.py", line 1644, in field_definition
    column_type = self.get_column_type(field.get_db_field())
  File "/Users/jimd/Desktop/p/sec/parse4/lib/python3.4/site-packages/peewee.py", line 1119, in get_db_field
    return self.to_field.get_db_field()
AttributeError: 'CompositeKey' object has no attribute 'get_db_field'
```

I can get around it by making the tabB_id field increment so it'll be independent of the colB_data Boolean, and create a second id field that will be equal across the true/false sides of the boolean, but that just feels a little ugly in my third normalised heart.


Are there any plans to address this issue? Your `CompositeForeignKey` solution would put this into a better state than it is today.


Yeah...I'm not sure what to do really and have been wavering about doing anything really. Adding `CompositeForeignKey` just seemed like a big hack, to be honest. It doesn't really do anything except provide a handy way of accessing a related object, but you could just as easily implement this using a simple property.

What are the benefits of adding this to peewee?


@coleifer Being able to reference a foreign composite key is pretty useful, and peewee doesn't have much support for it. For example, suppose we're representing all employees in a company, which is composed of many subsidiaries. Each subsidiary has its own method of allocating employee IDs, therefore it's possible that two different employees from two different subsidiaries have the same employee ID, and thus the natural key for a table of employees of the parent company is a composite key of the subsidiary and the employee ID. So we have:

``` python
from peewee import *

db = SqliteDatabase('example.db')

class BaseModel(Model):
    class Meta:
        database = db

class Subsidiary(BaseModel):
    name = CharField()

class Employee(BaseModel):
    subsidiary = ForeignKeyField(Subsidiary)
    employee = IntegerField()

    class Meta:
        primary_key = CompositeKey('subsidiary', 'employee')

class CompanyHierarchy(BaseModel):
    manager = ForeignKeyField(Employee, related_name='subordinates')
    subordinate = ForeignKeyField(Employee, related_name='managers')

if __name__ == '__main__':
  Employee.create_table()
  CompanyHierarchy.create_table()
```

Creating the tables this way can make doing bulk updates much easier (e.g., using MySQL we could use `INSERT ... ON DUPLICATE KEY UPDATE ...` with the `employee` table), but peewee throws an exception when given this code.

While this bit of code may seem fairly contrived, a coworker ran into a very similar situation and we had to work around it in a non-optimal way by enforcing desired constraints in code instead of the database and losing the ability to use the aforementioned `INSERT ... ON DUPLICATE KEY UPDATE ...`.


Gotcha, I guess the weird thing there is that `CompanyHierarchy`, to match `Employee`, should really have two columns: `subsidiary_id` and `employee_id` instead of a single "composite foreign key". Then a unique constraint defined in the `CompanyHierarchy.Meta`.


I really just can't bring myself to add a `CompositeForeignKey` object, it feels too hacky.


Do you have anything new to add to this issue? I just came across it myself. :)


Same here, would be great to have it implemented

@coleifer I'd think the non-hacky thing to do would be to make foreign keys composite in general. So, even if it winds up being a bit hacky having foreign key fields as a child of fields and a separate CompositeForeignKey, it would still be a step in the right direction.

I have this issue too, it's still a problem. Is there any news?

I still dislike having some kind of a ForeignKeyField that points to a CompositePrimaryKey. You can always add a foreign key constraint and a handy property in-place of some kind of "CompositeForeignKeyField".