ForeignKey on non-PK field

[mciszczon]

Hello there!

Sorry for a nooby question, I guess I lack the proper knowledge about SQLAlchemy and Ormar, but I am struggling with one thing. I want to create a one to many relationship on my ormar models, but not using the default primary key/ID fields.

My use case is like this:

class User(ormar.Model):
    id: int = ormar.Integer(primary_key=True, auto_increment=True)
    username: str = ormar.String(unique=True)
    ...

class Relation(ormar.Model):
    id: int = ormar.Integer(primary_key=True, auto_increment=True)
    user: str = ormar.String()
    ...

The thing is that the Relation.user field maps to User.username field, but I am unable to create a relationship between these two models using ormar.ForeignKey. Is that even possible? Didn't find anything in the documentation, nor in discussions/issues.

Thanks for the help!

[SepehrBazyar]

Hi @mciszczon
When you use ForeignKey, the primary key field in the Relation model is the User model.

It is better to write a ForeignKey instead of the user field that you wrote as str and use the select_related method to get the username.

Otherwise, you will lose the features of JOIN and etc.

[mciszczon]

Thanks for the response, but I was specifically asking whether it is possible, because this is a database level restraint that I have to work with and just hoped maybe I could make my life easier, lol.

I know it's not a good approach, but someone got a great idea to have a MySQL database with not a single relationship in it 🤣

[SepehrBazyar]

Thanks for the response, but I was specifically asking whether it is possible, because this is a database level restraint that I have to work with and just hoped maybe I could make my life easier, lol.

I know it's not a good approach, but someone got a great idea to have a MySQL database with not a single relationship in it rofl

You can write a @classmethod that takes an user model as a parameter, and then sends the username value of that model when the objects.create()` method is called.