4

I have a model with a foreign key that references the username field of auth.User. The original field has a maximum length of 150. But Django generates a foreign key with a maximum length of 30.

In my app's models.py:

class Profile(models.Model):
    user = models.ForeignKey('auth.User', to_field='username')

In django.contrib.auth.models:

username = models.CharField(
    _('username'),
    max_length=150,

Generated SQL:

CREATE TABLE "myapp_profile" (
    "id" integer NOT NULL PRIMARY KEY AUTOINCREMENT,
    "user_id" varchar(30) NOT NULL REFERENCES "auth_user" ("username")
);

This only happens when referencing auth.User.username. If I reference a long field in my own model, the foreign key is generated fine.

Why is that? How can I overcome it?

Using Django 1.11.4 and Python 3.6.2. I tried PostgreSQL and SQLite and the problem occurs on both.

CLARIFICATION: From the answers so far I think my question was misunderstood. I am not looking for a way to have long usernames. My problem is that the stock User model that comes with Django has one max_length (150), but when your model refers to it, the foreign hey has a shorter max_length of 30. Therefore if a user is registered with a username of 31 characters, I will not be able to create child objects of that user, because the foreign key constraint will be violated. And I need this because I have a REST API whose URLs nest resources under uses, that are referred by username, not ID. For example: /users/<username>/profiles/...

UPDATE: I think the reason for this behavior is the undocumented swappable property of the User model. It is designed to be replaceable by custom models. However, the configured model must have its data in the initial migration of the app that defines the model. The migrations code seems to generate references to the initial migration of swappable models. I am using the default User model, and its initial migration sets the username to 30 chars. Hence my username FKs are 30 chars long. I am able to work around this with a RunSQL migration to alter the FK data type to varchar(15), but I am in doubt if it's the right thing to do.

vsemionov
  • 41
  • 4
  • Why are you doing this? Normally a foreign key points to the primary key of the foreign table. Why do you need to change it? – Daniel Roseman Aug 28 '17 at 11:59
  • I have a REST API with objects nested under User objects. The URLs have the form /users//resources/. In this case the right foreign key to find the parent user is the username. – vsemionov Aug 28 '17 at 13:16
  • That doesn't follow, and those things are not related. It's perfectly possible - indeed normal - to have a nested relationship with fks as I describe. – Daniel Roseman Aug 28 '17 at 14:08
  • This is a very good point and it got me thinking about a design decision that I took a long time ago. The reason I reference parent objects by the field that is given in URLs, instead of their integer primary keys, is for query optimization. Suppose you get a request for /users//resources/ and you have to return a list. If your resource model references users by username, the query would simply be `select * from resources where username = `. But if the FK is to the integer id, you would have to do a join with the users table. – vsemionov Aug 28 '17 at 15:02
  • Also in my API, I want the `user` field of the returned objects to contain the username, so it can be used to construct URLs. Due to specifics of djangorestframework, when serializing the returned model instances, if the source of the `user` field is not present in the model, it has to look it up in the parent, once per object. 200 returned objects means 200 queries per request. The workaround is to annotate the `resource` objects with the username and use that as the source for the `user` field when serializing results, but that is additional complexity. – vsemionov Aug 28 '17 at 15:07
  • To avoid all that, I decided to use FKs that correspond to URL parameters and to output fields. I believe it is the correct approach, but if you have a better idea, please share it. – vsemionov Aug 28 '17 at 15:10

3 Answers3

1

Is recommended use short identifier, varchar(30) is a long number, something like 999999999999999999999999999999, when Django make identifiers always use the same number. I don't think that you are going to use so much users if you reach that number you should create another type of identifier. Remember the long of the user_id field is the id of the username and not the string

Mauricio Cortazar
  • 4,049
  • 2
  • 17
  • 27
  • The parent User already has a username that is varchar(150). Even if I have one user with a username that is 31 characters long, the application will not be able to insert resources that refer to it due to the foreign key constraint. – vsemionov Aug 28 '17 at 13:20
  • class Profile(models.Model): user = models.ForeignKey('auth.User', to_field='username') – vsemionov Aug 28 '17 at 17:13
  • Try deleting to_field=username and tell me – Mauricio Cortazar Aug 28 '17 at 17:17
0

You can use this hack described in this SO answer, but be very careful!.

Or you can use this package.

However, I think that, as described in this discussion, the best way would be to create a custom User model and do whatever you want there.

Hope it helps!

Jahongir Rahmonov
  • 13,083
  • 10
  • 47
  • 91
0

You must use custom user model.Taken from django docs.

150 characters or fewer. Usernames may contain alphanumeric, _, @, +, . and - characters.

The max_length should be sufficient for many use cases. If you need a longer length, please use a custom user model. If you use MySQL with the utf8mb4 encoding (recommended for proper Unicode support), specify at most max_length=191 because MySQL can only create unique indexes with 191 characters in that case by default.

Gayathri
  • 140
  • 11