CompositeKey as a foreign key in peewee with mysql

Question

I'm using peewee as my ORM for mysql DB. I have 3 tables in my scheme, one for devices, one for apps and one for results per device per tester app and tested app. the APPS table looks like:

package name | version name | version code |apk name

the 3 first columns are my primary key since i want every revision in my table and i want it to be easy to filter apps according to certain version code (version code is incremented with revisions in git\svn while version name represents the version itself as taken from the development branch). My problem starts when i want to have the APPS table as a reference table for my TESTS table, meaning each test refers to the APPS twice, once for the tester and once for the tested app. I'm not sure if it's such a good idea to have a 3 fields foreign key (which makes it 6!) in my TESTS table.

Any good solution for that ? I tried adding _ID field with auto increment as a 'KEY' so i'll have a numeric single field to access, but the ORM doesn't really supports it and i'm kinda gritting my teeth trying to pull this off.

Is my Db just organized bad or i need to simply replace ORM ? i think that without the ORM i would probably pull it off pretty easily...

score 0 · Accepted Answer · answered Feb 16 '14 at 18:11

0

Options:

Define an auto incremented primary key in the APPS table.
Define composite unique key in APPS table on pkg, ver, and vcode columns.
Use the primary key value of this table as FK reference in child table.

answered Feb 16 '14 at 18:11

Ravinder Reddy

23,692
6
52
82

1

looks like a good solution for me! however any idea how i implement this kind of behavior with peewee ? , again i can implement my own solution that would work in MYSQL bu i can't implement with peewee. – codeScriber Feb 17 '14 at 06:22
I am still an infant in Python. Can't give ideas on its implementations. – Ravinder Reddy Feb 17 '14 at 08:17
ok, got it, if worst comes to worst i can use: Apps.create_table() that will create auto incremented regualr table with no contrain and then Apps.getdatabase().execute('ALTER TABLE `APPS` ADD CONSTRAINT `limit_apps` UNIQUE (`pkg_name`, `version_name`, `version_code`); then i'll accept your answer, 10x! – codeScriber Feb 17 '14 at 08:27

score -1 · Answer 2 · answered Aug 28 '15 at 19:26

This is a very late answer, but I want to contribute the code for telling peewee about a composite key, something like the following. When this construct is used, Peewee does NOT add an "id" column to the table:

class SillyTable(peewee.Model):
    field1 = peewee.IntegerField(null=False)
    field2 = peewee.IntegerField(null=False)
    # Composite key, no "id" column needed.
    class Meta:
        primary_key = peewee.CompositeKey('field1','field2')

I'm using peewee v2.6.3 and PyMySQL v0.6.3 to access MySQL.

CompositeKey as a foreign key in peewee with mysql

2 Answers2