Default value in sql database design

Question

Is it always good practice to provide default values for integer-like fields? I use linq for database access.

Answer 1

It's a good idea if these fields are the sort that MUST have some value, and where a default makes sense. For example, a field named personAge might not make sense to have a default (can you reasonably assume that all your person records will have the same age, unless they specify otherwise?). It might be better to let it be nullable, and then handle errors when missing data appears.

Answer 2

You should provide default values only when it makes sense to do so, ie. when a field should have a specific value if you do not explicitly specify otherwise. E.g. a "Created" datetime field should have a GetDate() default value, but a "BirthDay" field should have no default value. It is better to make a field NULLable and set it to NULL rather than use a default that makes no sense.

The type of the field does not matter in selecting a default value.

Answer 3

Default values in the database are a double edged sword. They can make life convenient by removing the need to explicitly set the value to create a record. Once they're established though, it's very hard to track down dependencies on those values if there is a need to change them. For that reason, I steer away from having defaults in the DB.

Answer 4

I assume you are using linq-to-sql (even though the question is tagged only linq).

I would definitely advice against using default values in the db. The linq-to-sql layer (at least if generated with sqlmetal, I assume it is the same for the designer) will not use the default values from the database, but rather the default value for the C# type - which means 0 for numeric types. In that case having default values which are not honored by the code will just cause confusion.

Answer 5

Default values in a database may be needed to ensure that all records get values when the field does not and should not allow nulls. Sometimes a default value may be added when a field is changed from nullable to not nullable in order to provde values for existing records. That said, defaults should only be applied if they make sense. A default value for RecordCreatedDate makes sense, then you know it will get populated whether the application knows to fill in the field or not. A default value for number of items in the order doesn't make sense and should return an error to the application if it wasn't filled in.

Using default values to avoid having nulls where nulls are appropriate is a very poor practice in my opinion. Setting an integer in particular to a default value of 0 if you don't know what it should be is stupid and short-sighted as there could be actual 0 values. Null means unknown and if the data cannot be expected to be known at the time of data entry it is a very bad practice to make the column not nullable and put in a default value especially for integers and dates. Now you don't know if the value is genuinely zero or only a placeholder until you know the value. Now you have to write special rules to accomdate for fake values (especially if you put a fake value in for the date of something like '19000101'). Never use default values in the database just to avoid having nullable columns.