Fake Scenario

There is a software that was released 1 year ago. The software is to map and register all kind of animals on our planet. When the software was released, the client only needed to know the scientific name of the animal, a flag if it is in risk of extinction and a scale of dangerous(that is a fake software and specification, I don't want to discuss this here).

There are already 100.000 animals records saved on DB.

New Feature

One year later, the client wants a new feature. It is really important to him to know the animals classes, and this is a required field. So he asks me to put a field to input the animal class, and this field is required. Or maybe where this animal was discovered.


I have already 100.000 recorded animals without a class or where it was discovered, but I need to insert a new column to storage this information and this column can't be null. I don't have a default value for this situation (there isn't a default animal class or where it was discovered). I don't want to keep the requirement rule only on my software, my DB must have this requirement too(I like to keep business rules on DB too).

What are the alternatives to solve this situation?

I am on a situation that this new feature cannot be previewed or reviewed for the existing records. The time already passed and I can't go back on time to get it

  • 1
    ask him to populate the table with all missing classes during deployment Commented Oct 25, 2013 at 12:24
  • 2
    You could ask him to provide a file (Excel sheet or whatever, convert as needed) with this data for all animals already added. Then generate some code that updates the existing records. Commented Oct 25, 2013 at 12:26
  • 1
    There are really only two possbilities. Either create the column as NOT NULL and use a dummy value as the default, which will be overwritten before the system becomes usable after the update. Or create it as NULL, load the data when they are available, and ALTER the column to be NOT NULL afterwards. We don't have enough info to tell you which to choose. Commented Oct 25, 2013 at 12:38
  • This is a plausive solution if the new required field can be previewed or discovered. Imagine if the required field was an invoce date shipment? In some situations I cant "go back on time" to set that value. I would like to know what to do on those situations.
    – Tiago B
    Commented Oct 25, 2013 at 12:40
  • 2
    Had this scenario happen on a project I'm on. We had 300K "person" records in a database, the client decides to add "DateOfBirth" and make it a 'required' field. But remember, 'required field' does not equal 'non-nullable column'. In our case, we added the column as a nullable DateTime, but the UI requires the user to enter a DOB if they try to edit that record in any other way, and once a DOB is added, you can't erase it. The column will probably stay nullable forever since its unlikely the users will backfill 100% of the missing DOBs.
    – GHP
    Commented Oct 25, 2013 at 13:02

1 Answer 1


As others have suggested having your client supply the missing data is the best option.

Failing that, another idea would be to set a default -- such as "NOCLASS" that you could easily identify as invalid / default / pre-existing data. You could even wire the UI to force the user to update this if they update a record.

  • On my situation my client cannot supply the missing data because it is not easy as animal class. But the suggestion of a "UnkownMissingData" is plausive.
    – Tiago B
    Commented Oct 25, 2013 at 13:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.