Say we have a tabled called Account that has a foreign key FK_CurencyID, is it acceptable to hard set the default value of the FK_CurrencyID?
For example, if we wanted the default currency of all accounts to be GBP. In the currency table, GBP has a primary key of 50. The primary key data type is an int identity(1,1) field.
The problem I see see with setting the default of Account.FK_CurrencyID to 50 is that:
- Since the PK of currency is an identity, the PK of GBP may be different across instances (e.g. It may be different on Live, UAT and on any of the developers local machines). There are ways to force the PK to be 50 for GBP initially so we can work around this as long as nothing changes.
- Since the currency table may change, we could end up in a situation where we have to delete GBP as an option, which would cause the system to fall down until we update the default value.
I think the problem ultimately comes down to mixing / coupling code & system design with data (is there a word for this?)
Is it acceptable to set the default foreign key value to a hard coded ID or should we remove the default and have it handled in the logic layer of the application?
Best Answer
It is acceptable, and you can prevent some of your concerns by making sure the database creation script includes inserting the default values in your data, with a specific ID... that is what
is for.
Of course, as you mention, there is a trade off, once you decide the default, you are constraining your system against some possible future requirements. But all decisions come to trade offs, and any change to such defaults should be properly evaluated on any case.
The good part about setting it as a default constraint is that it is self-documenting, and the intention is obvious enough. The scenarios you mention would show up right away as referential integrity errors.