sql database design default value

is it nesessary to开发者_运维百科 add a default value to int,bit,datetime... fields? what's the benefit of having a default value?

A default constraint just means that if either no value or NULL are provided in an INSERT or UPDATE statement, the DEFAULT constraint value will be used.

Is it necessary to have them defined? That depends entirely on what the business rules are for the column(s) involved.

It's only necessary when a field is marked as not nullable. The benefit is that for a new row, you don't have to explicitly enter values for every field. This can be especially helpful when adding new fields to an existing schema - you would avoid having to go back and update all your code.

Edit - sorry, here's what I was thinking of. It's necessary when adding a field that is not nullable to a non-empty table. MySQL will assign a default value to all fields marked not nullable; not sure about SQL Server.

It's not necessary.

1. If you add a column to a table with existing data, the new column will be initialized. Example: you did not collect some data item for a while, then you started doing so. For the preexisting records, you want 'N/A' or zero or something.

2. If you want the same value for a column when a row is inserted and there's a default, you don't have to spell it out in the SQL. Example: all SO users start off with zero reputation.

if you are not allowing nulls in a field (especially a new one added to an existing table) it is a good idea to add a default value. if you do not, existing INSERT code that does not contain the new field will fail because the field's value will be considered null. its also useful on a field like a time-stamp so you don't have to code the value on every insert.

-don

The main use is enforcing your domain model, i.e. if a column should be zero rather than null, and if the column is marked as non-nullable then it will either need a default value (preferable) or be enforced at the application layer.

One benefit is that it simplifies your code (i.e. don't worry about handling nulls)

A bit field should almost always be non-nullable and have a default.

Useful for two reasons, if you expect 90% of the entries to have the same value e.g. You can default the LANGUAGE column to "EN" and save 90% of your users typing in the two characters. This is escpecially recommend where a foriegn key relationship exists if LANGUAGE was a blank then the insert would fail when referential integrity was enforced - or rows would dissapear if an inner join was made to the LANGUAGES table.

Secondly -- as mention above when adding a new column to a table you need to give it a default value if the column is not nullable.