Is it possible to have a ForeignKey field with a datatype other than int?

By default, django is going to make an FK an int.

I currently have a date dim table - meaning every record has a unique date tied to it. There will never be a record with the same date, and the concept of this table is to never be updated once initial data is added. Think - calendar table.

I have an instance where it would make sense to have a date_dim table where the PK is a date data type. Then have an event table tied back to the date_dim using an FK tied to the PK. Is it possible to set the data type of a Foreign Key field in django models?

Or is this an instance where I would need to set managed=false and then just set the FK up on the database end myself?


Yes, it’s possible to define a table with a non-int PK.

Yes, it’s possible to relate an FK to a non PK-field.

The first option (set the date as the PK) is _far_superior to the second.

I’m about to read through it, but to be clear what I am asking;

  • Say I have a scenario where I had a table called calendar where the PK is a Date data type.

  • Then I have a table called calendar_event with an FK going back to the PK in calendar

  • So I would have two Models - Calendar and CalendarEvent

My question;

In the CalendarEvent Model, is it possible to have a ForeignKey() where the datatype is not int.

A ForeignKey is not an integer. A ForeignKey is the index (key) being used to access the foreign table.

Your original premise:

is incorrect.

Ken, I don’t think you’re following me.

I am talking about the DB Column and the DB data type. A FK is associated with a column - and that column is going to have a datatype associated with it.

When I tried the above scenario - django complained because it tried to make my FK an int rather than a date.

As far as my premise being wrong, you’re going to have to be a bit more clear. because what I am seeing is django trying to give the following an int datatype.

calendar = models.ForeignKey(Calendar, on_delete=models.CASCADE)

Yes, I’m following you exactly.

In your example, in your Calendar table, what is the datatype of the primary key?

Please see the above quote.

Ok, let me rephrase it then.

Please post your Calendar model.

Class Calendar(models.Model):
    #calendar_id = models.IntegerField(primary_key=True)
    calendar_id = models.DateField(primary_key=True)
    epoch = models.BigIntegerField()
    day_suffix = models.CharField(max_length=4)
    day_name = models.CharField(max_length=9)
    day_of_week = models.IntegerField()
    day_of_week_iso = models.IntegerField()
    day_of_month = models.IntegerField()
    day_of_quarter = models.IntegerField()
    day_of_year = models.IntegerField()
    week_of_month = models.IntegerField()
    week_of_year = models.IntegerField()
    week_of_year_iso = models.CharField(max_length=10)
    month_actual = models.IntegerField()
    month_name = models.CharField(max_length=9)
    month_name_abbreviated = models.CharField(max_length=3)
    quarter_actual = models.IntegerField()
    quarter_name = models.CharField(max_length=9)
    year_actual = models.IntegerField()
    year_actual_iso = models.IntegerField()
    first_day_of_week = models.DateField()
    last_day_of_week = models.DateField()
    first_day_of_month = models.DateField()
    last_day_of_month = models.DateField()
    first_day_of_quarter = models.DateField()
    last_day_of_quarter = models.DateField()
    first_day_of_year = models.DateField()
    last_day_of_year = models.DateField()
    mmyyyy = models.CharField(max_length=6)
    mmddyyyy = models.CharField(max_length=10)
    weekend_indr = models.BooleanField()

    class Meta:
        managed = True
        db_table = 'test_calendar'

What is the datatype of the primary key of this table?

See above class / edit.

Now, given that you have changed primary keys, have you, at the database level, dropped and recreated these tables? (Makemigrations / migrate)

The above was all created from nothing. Keep in mind, I am not really trying to solve anything. I think that might help steer your thinking. If you look at the above class - that is literally how it is in the database. I created a new app and used the above model along with;

class CalendarEvent(models.Model):
    event_calendar_id = models.AutoField(primary_key = True)
    calendar = models.ForeignKey(Calendar, on_delete=models.CASCADE)
    event = models.ForeignKey(Event, on_delete=models.CASCADE)
    event_startend = models.ForeignKey(EventStartend, on_delete=models.CASCADE, null=True)

    class Meta:
        managed = True
        db_table = 'calendar_event'

You;re saying - django should have looked at the PK in Calendar and just automatically made calendar_event.calendar_id a Date datatype for me? Because it gave me an error saying it was trying to convert it to an int. If that is what you are saying I can try it again.

Yes, that’s precisely the case.

The primary key is whatever datatype the primary key is defined as.

A foreign key is the datatype of whatever the field is being referenced.

I guess the only possible caveat to that would be a database that doesn’t support non-integer primary keys - but I don’t know of any with that limitation.
The other possibility would be a database that doesn’t have an intrinsic date data type - one that stores a date as an integer. Again, I don’t immediately know of any that restricted.

I don’t know if you’re making a joke here?

Thanks alot! I just created a new schema and app, then just tried again from start a second time and it worked. My FK column is a Date datatype.

Thanks again.

No, no intent of one anyway.

You define a primary key using the primary_key attribute. The data type of that column is going to be whatever the column is defined as. If you define a date field as the primary_key, the primary key column is going to be a date type.

It may have been clearer had I written:

The column in the database table being used as the primary key is the data type of the model field assigned as the primary key.