Is it acceptable to have a default value on a foreign key

Question

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:

1. 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.
2. 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?

Answer 1

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

   set identity_insert on

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.

Answer 2

Yes it is acceptable, as default value is something that tells what value it should have, if nothing is provided. Thinking about your concerns, i would still like to point out the fact that it is not always wise to set default foreign key value like this.

If you create user in the application, it should check what id does the default currency you have defined in the application to be, then create user with that.

Your application could do something like this:

-- Finding default currency you want    
SELECT @defaultcurrency = CurrencyId FROM Currency WHERE CurrencyName = 'GBP'

-- User creation:
INSERT INTO Account (...) VALUES(..., @defaultcurrency, ...)