Recently talking to someone about a persistence framework that they are using, this framework creates a rows in the database table with just Primary Key value and then later on updates the table row with values for other columns. Because of this framework, the tables cannot have any NOT-NULL constraints defined. For example, the framework is doing
You cannot have NON-NULL constraint defined on name or birthdate column, since the INSERT statement would blow up, forcing you to change table design
1 2 3 4 5 6 7 8
What can you do, so that table design does not suffer? and have better data quality constraints? one argument we see is that, application will constrain the data and not allow customers with null name or birthdate to be persisted. I have seen way too many projects where this argument fails after some time when someone starts to import customers from other system or starts to implement a service to push customers and now the constraint in the application layer is pretty useless allowing bad data to get in, since there is nothing stopping these bad rows from getting in the Customer table.
Differed constraints come to the rescue here in oracle (bet there is equivalent types of differed constraint checking for other database vendors) Using the Customer table example, create a differed constraint on Name and BirthDate columns, as shown below
1 2 3 4
1 2 3 4
now run the same Insert and Update shown above, they would run fine, but you would get a constraint violation error when you try to commit with a null Name or BirthDate.
The radical Idea, I’m trying to highlight is to be creative in you database design and use the features of the database to be able to enforce data quality constraints or other constraints and still not have to suffer on application development velocity.