[ACCEPTED]-Should a database table have default values?-column-defaults

Accepted answer
Score: 17

My rule: if many records will use that default 16 (at least initially) then I like to use 15 it as a default. For example, an image table 14 for products in an online store might have 13 a default path of images/NoPictureYet.png. Eventually, those will 12 get replaced, but for batch loads of data 11 where the pictures simply don't exist yet 10 (and maybe most of them never will!), a 9 default makes sense (to me, at least).

If 8 there is no sensible default (such as "first 7 name" in a customer database - I don't want 6 MY name defaulted to "FirstName"), then 5 I make it non-nullable and no default - it's 4 the application's responsibility to ensure 3 that a correct value gets entered.

But no 2 hard and fast rules on this. It all varies 1 a little ;)

Score: 7

One practical case where I personally found 10 good use of default values is for a last_modified column.

This 9 column is never updated by stored procedures 8 or business logic, but gets updated automatically 7 by triggers when any value in the row changes. However 6 it is also set to GETDATE() by default so that the 5 value of a new row would contain the timestamp 4 of when it was created, which basically 3 is when it was last modified.

ALTER TABLE users ADD CONSTRAINT dc_users_last_modified
                  DEFAULT GETDATE()
                  FOR last_modified;

The update 2 trigger would then look like something like 1 this:

CREATE TRIGGER trigUpdate_users 
ON users 
FOR UPDATE 
AS 
BEGIN 
    IF NOT UPDATE(last_modified) 
        UPDATE users SET last_modified = GETDATE() 
        WHERE user_id IN (SELECT user_id FROM inserted);
END 
GO
Score: 4

No hard and fast rule can be applied. It 4 depends on the columns. It may be totally 3 sensible to have a default order type, for 2 example, but the idea of a default for a 1 customer phone number doesn't make sense.

Score: 4

I would say its a gray area. Typically 11 I wouldn't design a new database with lots 10 of default values, but you often need to 9 use them when enhancing an existing system.

For 8 example adding a new non-null column to 7 an existing database. You might not want 6 to (or be able to) update all the code that 5 inserts into that table, so you would need 4 to put a default on it to ensure that any 3 "legacy" code can still insert data (assuming 2 the default value is appropriate for the 1 legacy code of course).

Score: 4

tl;dr: Default values are business logic, and 13 I want business logic in the object model. As 12 such, a database cannot contain default 11 values.

E.g. In the database I have a bit 10 field: IsANicePerson. This field translates 9 to a property on the Person class. Being 8 optimistic by nature, I want the default 7 value for this property to be 'true'. So 6 in the Person class I implement this (as 5 the default value of the isANicePerson backing 4 field). If I would allow default values 3 in the database I would have to duplicate 2 this logic. Duplicate code/logic is bad. Hence 1 my objection to default values.

Disclaimer: I live in an OO-world and use Linq2Sql.

Score: 3

Default values are important if the column 25 must have a value (it is not null). In fact 24 if you are changing a column from allowing 23 nulls to not allowing them a default value 22 is pretty much necessary as not all existing 21 code may populate a value. Sometimes in 20 this case the default value is something 19 like 'UNKNOWN'. This is especially true 18 if you want to use the WITH VALUES to provide 17 the default values to exisiting records 16 where the field is null in the ALTER TABLE 15 statement that changes the column to NOT 14 NULL

Default values are critical for fields 13 that the user interface typically doesn't 12 deal with. For instance we have default 11 values on date_inserted and user_inserted 10 columns that the user never even knows are 9 there. This is especially critical if many 8 different applications could populate the 7 data to ensure no one forgets about these 6 columns.

Then there are columns which are 5 typically given a value on data entry that 4 may change later. Things like a status column.

Many 3 columns can't really have a default though. What 2 would be the default address or name of 1 a user?

Score: 2

It should be pretty simple. If the data 6 should usually be unique for each row like 5 customer phone number or the default value 4 will most likely change over time then I 3 wouldn't use it. That means it is really 2 only useful for filling CreateDate, ModifiedDate, or 1 other columns of that nature.

Score: 2

Here are the guidelines I personally use 50 regarding default values which have served 49 me well in the past. In the following examples, consider 48 a database backend with multiple applications 47 with read/write access to the backend. In 46 these cases it is essential that the database 45 define how the data is to be modelled and 44 therefore ensure data integrity.

1) CreatedDate 43 and ModifiedDate columns. These columns 42 typically will have getdate() (sql server) defined 41 as the default. As mentioned in other posts, these 40 fields can then be updated with triggers 39 etc.

2) Boolean state columns. Examples: "IsDefault", "IsDeleted" (for 38 auditing), "IsActive",etc. All of these 37 fields will generally have a logical default 36 state which should be defined by the data 35 model. Exceptions to this would obviously 34 be nullable tri-state boolean fields where 33 the null state represents something about 32 the data stored in the record.

3) Data constraint 31 definitions: Columns with AllowNull=false 30 and no default defined. In other words, a 29 value is required by the application.

4) Lookup 28 table foreign key identities: This is probably 27 not the norm but for alot of lookup table 26 foreign keys I will define a default that 25 covers the initial state of a record. So 24 for example, in an "Event" table the foreign 23 key column "EventTypeId"(int-autoincrement) will 22 have default 1 and represent "General" or 21 something. This will cover most scenarios 20 where, for instance, I want to log an event 19 but dont care about a specific type id.

5) Non-critical 18 string columns: "Description", "Comment" etc. For 17 these columns I will generally define '' as 16 the default purely to simpify System.DbNull=>Null 15 conversion handling in applications. This 14 is something that may not be applicable 13 in all scenarios especially when the table 12 concerned contains millions of rows and 11 storage space is an issue.

So in summary, use 10 defaults to ensure the data integrity of 9 the actual data stored in the database. The 8 data model should define these data integrity 7 rules within itself and any application 6 interacting with it will and should be forced 5 to respect these rules. Also please note 4 that this is not doctrine and that there 3 will always be exceptions. Consider each 2 scenario individually so that it makes sense 1 for your database/application.

Score: 1

default values are useful for audit purposes. If 8 the application does not include values 7 for the field than the default value is 6 useful, for example date time stamp on last 5 updated. As rule the applications should 4 be responsible for providing required fields. The 3 database will throw errors if the required 2 fields are not provided. You don't want 1 magic coding because that can be confusing

Score: 0

Definitely a gray area. It is the classic 7 how much business logic to put into the 6 database question.

If we wanted to be purist 5 and say no business logic belongs in the 4 database then the answer would be never 3 use them.

Being practical we can make an 2 exception as we often do and allow the logic 1 of a default into the database.

More Related questions