The moose likes Oracle/OAS and the fly likes Constraints or defaults are maintenance nightmare? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "Constraints or defaults are maintenance nightmare?" Watch "Constraints or defaults are maintenance nightmare?" New topic

Constraints or defaults are maintenance nightmare?

Mark Spritzler

Joined: Feb 05, 2001
Posts: 17276

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?


Perfect World Programming, LLC - iOS Apps
How to Ask Questions the Smart Way FAQ
Lasse Koskela

Joined: Jan 23, 2002
Posts: 11962
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).

Author of Test Driven (2007) and Effective Unit Testing (2013) [Blog] [HowToAskQuestionsOnJavaRanch]
Ernest Friedman-Hill
author and iconoclast

Joined: Jul 08, 2003
Posts: 24199

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.

[Jess in Action][AskingGoodQuestions]
Ilja Preuss

Joined: Jul 11, 2001
Posts: 14112
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
Gregg Bolinger
GenRocket Founder
Ranch Hand

Joined: Jul 11, 2001
Posts: 15302

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.

GenRocket - Experts at Building Test Data
Jeanne Boyarsky
author & internet detective

Joined: May 26, 2003
Posts: 33106

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.

[OCA 8 book] [Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Other Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, TOGAF part 1 and part 2
Andrew Monkhouse
author and jackaroo
Marshal Commander

Joined: Mar 28, 2003
Posts: 11776

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

The Sun Certified Java Developer Exam with J2SE 5: paper version from Amazon, PDF from Apress, Online reference: Books 24x7 Personal blog
I agree. Here's the link:
subject: Constraints or defaults are maintenance nightmare?
It's not a secret anymore!