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).
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.
The soul is dyed the color of its thoughts. Think only on those things that are in line with your principles and can bear the light of day. The content of your character is your choice. Day by day, what you do is who you become. Your integrity is your destiny - it is the light that guides your way. - Heraclitus
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.
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.
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.