Constraints or defaults are maintenance nightmare?

[Mark Spritzler]

OK, my manager told me to remove my default in the following column

create_date DEFAULT current_date NOT NULL,


He stated that defaults and constraints like only "A, B, C" are the only possible values, are maintenance nightmares.

Now I have designed databases for over 13 years, 5 with Oracle, and have worked on projects with over 2000 tables. I have never ever heard this to be a maintenance nightmare.

But I am open to other opinions and experiences and won't discount his knowledge, he is a smart guy.

Has anyone ever heard them to be a maintenance nightmare?

Mark

[Lasse Koskela]

Nope. Can't say that I would've ever had maintenance problems because of a default value for a column exists (assuming the default value makes sense for the application in question -- but that's not a problem with default values per se).

[Ernest Friedman-Hill]

I don't have anywhere near your level of experience in this area, but in my experience it's null fields that are a nightmare; defaults simplify code and therefore make it easier to maintain.

[Ilja Preuss]

Did he explain why he thinks they are maintenance problems? I suppose constrains can be if updating the schema is hard. If that isn't a problem, I'd actually say that constrains *help* in maintaining data integrity.

I have more mixed feelings about default values. Default values in the database can hide bugs when someone forgets to provide a necessary value in an insert statement. I'd at least want to have all default values handled by the same application layer, and I guess that the database itself might not be the best fit in many situations.

[Gregg Bolinger]

I don't see any problems with DEFAULT unless the date needs to be null at any time. For example, I have a table with columns of date_created, date_modified, date_accepted, date_closed...

date_accepted and date_closed should be null until such time as record has been accepted and/or closed. So those don't have a DEFAULT. However, date_created and date_modified do have a DEFAULT date of the current date when the record is created.

[Jeanne Boyarsky]

Originally posted by Mark Spritzler:
He stated that defaults and constraints like only "A, B, C" are the only possible values, are maintenance nightmares.

I agree that constraints like only "A, B, C" are maintenance nightmares. If someone passes another value, the code throws a (often uninformative) exception. Defaults aren't typically as bad and can be useful.

In this case, the default acts as a failsafe in case the user forgot to pass a date. In fact, you could even argue that it is preferable encourage users to let Oracle generate the date since the user could pass a different date. This would create a false view of when the record was created.

[Andrew Monkhouse]

In defence of removing the default values:

If you have a NOT NULL constraint, and you remove the default value, then if some coder forgets to set the field when inserting a row they will get an error. Similar to the JVM throwing a NullPointerException - the coder should not allow the exception to be thrown in the first place, but hopefully you will notice it during system testing. This is basically pandering to bad coding (the coder failed to check all the fields), but it can be a final sanity check. The downside is that if system testing doesn't show the problem, you could get code getting into production with the problem - and then your users will dislike you even more (hey, I think DBA's exist to be disliked )

If you do not have the NOT NULL constraint, on a field that is supposed to be set by the user but you do have a DEFAULT value, then it can be harder at a later date to work out which records have incorrect data. That is, 2 years from now I am looking at the code, and I can see that if users enter data through routine 'x' then the data is set correctly, but if they enter data through routine 'y' then the data is not set correctly. I now need to go and find all the bad records. With a DEFAULT value set I am not going to be able to find them. The counter arguments could be: how often is this likely to happen / what am I going to do once I do find all the bad records?

To give an example, if the field was birth_date on a savings account, and you have a DEFAULT of current_date, then when looking at your records 6 months from now, if you find a record with a date of August 05, 2005 does this mean that it is an account created for a new born baby (which could be valid) or is it an account created for a 40 year old person where the programmer forgot to set the birth_date field? If there was no DEFAULT, I could spot that this is a problem record and contact the account holder and ask them to update their records.

I agree with Jeanne that there are some fields that you want DEFAULT values for and/or triggers. Creation date and last modification date are two simple examples: I really don't want users of the database entering any value they like into those fields.

Regards, Andrew