default value vs. null for foreign key_问答_开发者

I have a question about using null vs. default value for foreign key columns in database. I found a lot of opposite opinions about null vs. default values when designing databases but not exactly for foreign keys (what are main pros and cons).

Currently I'm designing a new database which will store a lot of data for different web applications and other systems with different data access approaches (ORM, stored procedures) and I want to implement general rules on the lowest level as possible (database). (So not to worry about this rules later in applications).

To give you an example let's say that I have a table of users User with foreign key column for his nationality NationalityID which is a primary key CountryID for table Country.

Now I have two/three options:

A: I allow N开发者_StackOverflowationalityID column (and all other similar foreign key columns in database) to be null and just stick with common approach of checking always and everywhere for null (applying rules in application)

B: I assign a default value for every foreign key to be let's say "-1" and put in every relation table additional column with "-1" as a key and all other data as "No data" (for this example in Country table I put column with CountryID of "-1" and for CountryName I set "No data"). So every time I will want to know users nationality I will always get result without additional code rules (no need for me to check if it's null or not).

C: I can disallow null value for foreign keys. But this is really something what I want to avoid. (I need to have an option to store at least basic data (users name) if not the additional data (users nationality))

So is B good approach or not? What am I missing here? Do I lose more that I gain with this approach? Which problems could I have (in addition to be careful to always have additional column in relational tables with ID value of "-1" which says there is "No data")?

What is your good/bad experience with foreign key default values?

thank you

If you normalize this won't be an issue.

Instead of putting nationality in the USER table, make a User_Nationality table that links users to Country_ID in the other table.

If they have an entry in that lookup table, great. If not, you don't need to store a NULL or default value for it.

You need to enforce FK relationships, and allowing NULL goes against that. You also don't want to make up information that may not be accurate just to populate a field, which negates the point of requiring the field in the first place.

Use lookup tables and you can bypass that entirely.

This will also allow you to change your mind and choose one of your options down the road.

If you use views, you can choose to treat missing data as a NULL or a default value without needing to alter the underlying data.

Personally, I would feel that even if you have a non-entry entry in your database with a key of -1, you would still be performing a check to see whether you want to display 'No Data' or not for each individual field.

I would stick to NULLs. NULL is meant to mean the absence of data, which is the case here.

B is a terrible approach. It is easier to remeber to handle nulls than to have to figure out what magic number you used and then you still have to handle them. Use number 1. But I like JNKs idea best.

I suggest option D. If not all users have a defined nationality then that information doesn't belong in the user table. Create a table called UserNationality keyed on UserId.

I like your B solution. Maybe it will be possible to map the values into other entities, so you have Country, and NullCountry that extends Country and is mapped to row with id=-1 and have special code in its methods to make it easy to handle special cases.

One problem is probably that it will be harder to do outer joins on that foreign key.

EDIT: no, there should be no problem with outer joins, because there would be no need to do outer joins.