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

Using Django
1

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?

Thanks.

2

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.

3

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.

4

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

Your original premise:

is incorrect.

5

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)

6

Yes, I’m following you exactly.

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

7

Please see the above quote.

8

Ok, let me rephrase it then.

Please post your Calendar model.

9 
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'
10

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

11

See above class / edit.

12

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

13

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.

14

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.

15

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.

16

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.

17

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.