Should optional Foreign Key be NULL or should it point to an empty string

Question

When the related value in foreign table is missing for an optional Foreign Key, I can either:

1. Set it to null

2. Point it to an empty string '' in the foreign table

It appears to me that if you follow Django design practices, you end up with option 2 (see code below). Is there any obvious advantage or downside with either approach?

Django favors 2 somewhat by design/convention. The docs say that null and '' are 2 possible values of "no-data". Hence if you omit the optional field, the forms will validate correctly and supply an empty string to which your Foreign Key can point to. However logically, it seems like a missing value should imply a null or missing Foreign Key (instead of a valid Foreign Key that points to an empty value).

Moreover, storing blank would be a gotcha if I simply list all albums or do a count. Each time I've to remember to avoid the empty string. On the contrary, a null foreign key would never have an entry in the album table.

Reference:

1. Can a foreign key be NULL...
2. Can foreign key be NULL
3. Django docs on null vs blank

---

Optional detail with code:

#models.py
class Album(Model):
    name = CharField(max_length=50, unique=True)

class Song(Model):
    name = CharField(max_length=50) 
    album = ForeignKey(Album, null=True, blank=True)    

#forms.py
class SongForm(Form):
    name = CharField(max_length=50)
    album = CharField(max_length=50, required=False)

If you have a song with no album name, the form returns {'name':'foo', 'album':''}. This creates an entry in Album table with a blank name. I can circumvent that in the view (see code below). But this seems like a hack since data validation should be done in forms.

if album:
    Song.objects.create(name=form.cleaned_data['name'], album=form.cleaned_data['album'])
else:
    Song.objects.create(name=form.cleaned_data['name'], album_id=None)

Answer 1

After thinking more, approach 2 (the one in which a missing foreign relation implies an FK to an empty string) has one advantage compared to approach 1.

Using approach 2would make it easier to have a unique_together index on (song.name, song.album). This is better explained with an example. When empty strings are used for Album, two similar values of Song (d below) would be caught by unique constraint. However, null are treated as different values in DB and they won't be caught in case 1 (have to rely on conditional indexes to make this work).

I'm not sure this was by design or an accidental advantage of Django's convention.

Missing album => FK is null                    Missing Album => FK points to blank name
Song  |   Album                                Song  |   Album
----------------                               ----------------
'a'   |    'x'                                 'a'   |    'x' 
'b'   |    'y'                                 'b'   |    'y'
'c'   |    'y'                                 'c'   |    'y'
'd'   |    null                                'd'   |    ''
'd'   |    null  <- Dup. not caught by DB      'd'   |    ''   <- Duplicate caught by DB

Answer 2

Django's convention is to use '' to represent the lack of data in text-based fields. When it comes to ForeignKeys, however, the convention in Django and elsewhere is to represent the lack of data by NULL (i.e. option 1).

Your option 2 confuses the distinction between an album with no name and a song with no album. Django's convention has to do with different ways to represent the lack of data in a field where that is allowed. But in your case, it is not valid to have an album with no name, so option 2 would require inventing a invalid album just to give other models something to point to.

You write: "Point it to an empty string '' in the foreign table". Note, though, that foreign keys don't point to fields in a foreign table, they point to entire rows. Imagine you had other fields in the Album model (a BooleanField is_international, for example, or a DateField release_date). Now you have to make up arbitrary, fake values for these fields in your magical row that doesn't represent an actual album.

So I suggest sticking with the conventional option 1.

Handling this in forms is built-in and straightforward. In ModelForms, for example, a ForeignKey is represented by a ModelChoiceField. If the ForeignKey has blank=True and null=True then one of the choices in the dropdown will be blank, and saving the form with that choice selected will make the corresponding database field NULL.

In your case it looks like you're having the user enter the album name directly on the Song form. That's perfectly fine, but of course you're going to have to have special logic to interpret that value and create the appropriate models. Your sample code above is not a hack and is not data validation. Validation is allowing empty values or not, and that is controlled by blank=True (in the model) or required=False (in the form).