CODEWITHSUNDEEP
sql-server
forme
forme

Reputation: 73

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?

Upvotes: 0

Views: 1644

Answers (6)

Seva Alekseyev
Seva Alekseyev

Reputation: 61361

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.

Upvotes: 1

danben
danben

Reputation: 83250

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.

Upvotes: 1

James Anderson
James Anderson

Reputation: 27478

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.

Upvotes: 0

OMG Ponies
OMG Ponies

Reputation: 332571

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.

Upvotes: 1

Mitch Wheat
Mitch Wheat

Reputation: 300559

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.

Upvotes: 0

Don Dickinson
Don Dickinson

Reputation: 6258

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

Upvotes: 0

Related Questions