Interesting behavior of the column default mechanism

This isn't going to blow anyone's socks off or stun anyone like a taser to the face, but this bit me in the ass this week so I am sharing while it is fresh in my mind and to hopefully save someone some pain in the future.

Oracle provides functionality to put in a default value in a column. This week, we had an issue where a partner was passing some information but missing a field that is relatively unimportant but a downstream process was depending on. A brilliant solution would be using the oracle column default mechanism...or so I thought. Let's say the Table involved is ADDRESS and the column is COUNTRY_CODE. Some new downstream process expects every row to have a country code filled in, but the table allows nulls in the column. Well, you can still use default. Here is how you configure a default.

alter table address modify (country_code default 'US');

Pretty straightforward really. It doesn't matter if the column is nullable or not. It works either way. Well, depending on how you access the table. And there lies the rub this time. You can insert a row and/or update a row and the mechanism works but you can insert a row and/or update a row and the mechanism does not work, depending on the syntax of the statement.

When inserting a row, one can specify the column list before the values statement to specify a subset of the overall table's column list or provide the values in a different order than how they are specified in the table. You do this such as:

insert into address( address_id, addr, addr2, city, state, zip, country_code) values (1, '123 Main', 'Apt B', 'Nowheresville', 'KS', '90210', null);

Or

insert into address( address_id, addr, addr2, city, state, zip) values (1, '123 Main', 'Apt B', 'Nowheresville', 'KS', '90210');

The kicker is that the first insert will not utilize the default mechanism while the second one will. In plainer terms, if your insert or update specifies a column with a default value on it, the default value will not get populated.

Similarly,

update address set addr2 = 'Apt C', country_code = null where address_id = 1;

...allows the null value to enter the table in the country_code column. But,

update address set addr2 = 'Apt C' where address_id = 1;

...would set the country_code to 'US' if it was previously null on the record.

If this behaved in a different manner, a tiny database script could have prevented changing application code and freed up application developers to work on other bugs and features. However, because the application code interacts with the table by including the country_code column in the insert and update whether or not there is a value specified for them, we cannot use the default mechanism.

I only had a little bit of egg on my face and frustration this week, after suggesting the default column value mechanism as a solution and subsequently finding out its application is dependent on the column list in the statement. If your application code is intelligent enough to dynamically list the columns being inserted or changed or your database activity is controlled by stored procedures so that the application would call I_ADDRESS or ADD_ADDRESS procedures to insert a record, you could dynamically choose your column list depending on the values passed to the procedure and utilize the default value mechanism.

Well, that is a short and sweet little post this week. I guess they can't all be earth shattering and brilliant. Use the default mechanism with care and it will help you.