Composite primary keys

[caniko]

Is your feature request related to a problem? Please describe. I must create a table where the primary key is a composite of fields, but this is not supported. Excerpt from the docs:

We currently support single (non-composite) primary keys of any indexable field type

Describe the solution you'd like A model like:

class Mutation(BaseDatasetTag):
    gene: str = fields.CharField(pk=True, max_length=80)
    knock_out: bool = fields.BooleanField(pk=True, default=False)
    knock_in: bool = fields.BooleanField(pk=True, default=False)
    method: str = fields.CharField(pk=True, max_length=200)

Describe alternatives you've considered There are none, but worse alternatives to this implementation of the table. I will have to use a UUID field, and I will have to filter every time I want to be certain the combinations of field values doesn't exist in my DB.

[long2ice]

What about unique_together?

[caniko]

That works! But I will still need to filter to get the UUID pk.

Will I be able to migrate my table to a composite key when it is implemented?

[caniko]

I am re-opening the issue, I really think this feature to be very important.

Will I be able to migrate my table to a composite key when it is implemented?

Would be nice to know

[caniko]

I am working towards a PR to solve this issue once and for all.

During my investigation, I noticed the TODO comment stating that @grigi wants to move the PK generation out of the schema generator. This comment was back in 2019.

Care to elaborate for me to proceed with my PR? While you are here, I'd like to know your opinion about implementing composite primary keys in tortoise-orm, and why it was not implemented in the first place. This is a core feature of SQL after all... Was the decision entirely arbitrary?

Thank you!

[VincentSch4rf]

I just ran across this issue, as I am dealing with an existing PostgreSQL database with the timescaledb extension installed. This forces you to include all partitioning columns in any UNIQUE INDEX. As in PostgreSQL, defining a PRIMARY KEY creates such a UNIQUE INDEX, you have to use composite keys if you want to have a PRIMARY KEY that is not identical with your time column.

I was very surprised, that composite keys are not yet supported. However, this issue seems to be rather old, did anyone attempt an implementation already? I might otherwise try to implement this feature.

[caniko]

I suggest switching to SQL Alchemy

[wling-art]

I also hope tortoise supports composite keys, we really, really need it in case of multiple primary keys!