What's an alternative solution to using a null value for a foreign key field?

Question

I have a few tables in my database which contain a Foreign Key field linking to another table. The problem is, the value for that field isn't always going to be present, and so sometimes I might have to insert a null foreign key field. To elaborate:

I have a table Sense_LanguageSource which lists individual etymologies of entries in a dictionary. This has the following fields:

• LanguageSourceID (PK - Autonumber)
• SenseFK (FK - Number)
• LanguageCode (FK - Number)
• SourceWord (Short text)
• LanguageSourceType (Short text)
• IsWaseieigo (Yes/No)

The LanguageCode field is the problematic case. It's used to list the language which the etymology derives from, and in this case it's a foreign key linking to a LanguageCodeValue table, which contains both the ISO-639-2B code and the full name of the language.

However, some of the etymologies I'm going to insert into the database don't have LanguageCodes, so I would have to insert a 0 into that field. Initially, Access wouldn't let me do this, so I disabled Referential Integrity. Then when I reopened the database, Access said that the Database was compromised and needed to be repaired. So I think that I need an alternative solution to this.

I would make it less normalised and simply have a text field for the LanguageCode in there, but there are two tables that refer to the LanguageCodeValue table, and it seems to make a lot of sense to have a table describing what each LanguageCode refers to. The solutions I can think of right now are:

1. Remove the relationship between the LanguageCode fields and the LanguageCodeValue table (so that it's purely a reference table, and not linked,) and just use SELECT queries if I need to look-up the meaning of the LanguageCode.

2. Keep the link between the tables without Referential Integrity to allow null foreign keys, and hope that the database doesn't break down.

3. Insert a ""null"" value in the LanguageCodeValue table to refer to (by which I mean an actual record with a key value of 0 or similar, and blank values for the text fields), so that instead of entering a null foreign key, I can just link to a record that doesn't mean anything.

4. Another solution?

As I'm new to databases and not sure about best design practices, I would like to know which solution is most appropriate in my situation.

Answer 1

As Akina said in the comments, try to avoid breaking relationships and referential integrity in your databases. If there is a documented need to have a value in there that indicates "none", "unknown", "other" or something like that, it is better to have that in your lookup table than try to hammer Access apart to allow for NULLs in a Foreign Key field.

Answer 2

Keep your referential integrity. Option #3 is your best choice here. However, instead of inserting a null value into LanguageCodeValue, create a new record with a key of -1 and value "Unknown". Any records inserting into Sense_LanguageSource with a null LanguageCode foreign key should use -1 as the key instead. Thus, any joins will show the LanguageCode value as "Unknown".