Valid uses for DEFAULT clause in a table column definition, besides sequence nextvalue()

Question

In a recent question, there's a debate whether or not an arbitrary default value in the definition of a column in a table is considered having business logic in the database. As we know, if an insert statement fails to provide a value for a given column, such default is inserted instead of null.

My question is, besides asigning a sequence nextvalue() to a PK (surrogate or otherwise), which is not a literal literal value:

• What uses of the default clause when defining a column are not considered having business logic in the database?

Answer 1

If the default is just that, a default value, that is not "business logic." If you insert a record and set an integer to zero, is that logic? Most modern languages (e.g. pretty much anything C-based) would default that to zero anyway. Does it really matter if you default it to one instead?

The way I see it, "insertion logic" goes in an "after insert" trigger. Code executes, it calculates a value, and sets it on the new record. For example: on insert, field C is set to the sum of fields A and B, instead of some fixed value such as zero. That is "insertion logic."

"Every new record gets a value of 'baz' in the 'foo' field if not already specified" is not really logic in anything but the most trivial sense of the word, certainly not in a complex program.

What uses of the default clause when defining a column are not considered having business logic in the database?

Since you specifically mention "default clause" which would mean "not a trigger," I say all uses of the default clause do not add logic to the database.

As an aside, most databases treat default values as default constraints. In other words, "null is outside of the acceptable values for this field so set it to something within the set of acceptable values instead." If you look at it that way, a default value is almost the same as specifying a range of values for a field. Set it outside that range (null), and you get a default value instead.

Answer 2

I think one of the most valuable uses of default values is setting a timestamp for record creation. Often these timestamps are provided but that means that there are different clocks involved so you might end up with records with timestamps that suggest they were added earlier than records that were actually there first. The database is a great arbitrator of the question 'what time is/was it?'

Answer 3

The field might not be used by a business logic (meaning application), but by business process (meaning people). For example INSERTED_WHEN or INSERTED_BY column with current timestamp or user as a default. Mainly for support and maintenance needs.

I do not agree with the assumption behind the question that a value is a logic. Logic is a process, value is not. Field value is not a part of business logic, the field is.