Passionate about data

Data and its implications on software design and development.

Frameworks Should Not Constrain Your Table Design

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

1
2
INSERT INTO customer (customerId, name, birthdate) VALUES (1,null,null);
UPDATE customer set name = 'FOO Name', birthdate = '12/12/1978' WHERE customerId = 1;

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
CREATE TABLE Customer (
CustomerId NUMBER NOT NULL,
Name VARCHAR2(40) NULL,
birthdate DATE NULL,
CONSTRAINT PK_Customer
      PRIMARY KEY (CustomerId)
)
/

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
ALTER TABLE Customer
    ADD (CONSTRAINT CHK_Customer_Name_NotNull
    CHECK (Name IS NOT NULL) DEFERRABLE INITIALLY DEFERRED)
/
1
2
3
4
ALTER TABLE Customer
    ADD (CONSTRAINT CHK_Customer_BirthDate_NotNull
    CHECK (BirthDate IS NOT NULL) DEFERRABLE INITIALLY DEFERRED)
/

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.

1
2
INSERT INTO customer (customerId, name, birthdate) VALUES (1,null,null);
UPDATE customer set name = 'FOO Name', birthdate = '12/12/1978' WHERE customerId = 1;

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.