Business Rule: Only One per Day, but Keep the Time

The business rule states: Only one entry is allowed per ID and per day and the time should be recorded. The table involved (simplified for the blog post) Wouldn’t it be nice if it was possible to do it like this? This way you still have the complete date information (time is a component of the date column), and only

The business rule states:

Only one entry is allowed per ID and per day and the time should be recorded.

The table involved (simplified for the blog post)

Wouldn’t it be nice if it was possible to do it like this?

This way you still have the complete date information (time is a component of the date column), and only use the TRUNC (inspection_dt) to constrain the data entry.  As you can tell from the error message, this is not allowed.

Oracle 11g Release 1 introduced Virtual Columns which can implement this requirement declaratively. A Virtual Column is a column which is based on an expression which can be used
in e.g. constraints – just what the doctor ordered.

You can read the full article on my blog: http://nuijten.blogspot.com/2011/08/business-rule-only-one-per-day-but-keep.html#more