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)

SQL> create table test
  2  (id         number
  3  ,inspection_dt date
  4  );

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

SQL> create table test
  2  (id         number
  3  ,inspection_dt date
  4  ,constraint one_per_day unique (id, trunc (inspection_dt))
  5  );
,constraint one_per_day unique (id, trunc (inspection_dt))
                                    *
ERROR at line 4:
ORA-00904: : invalid identifier

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.

SQL> create table test
  2  (id         number
  3  ,inspection_dt date
  4  ,inspection_day as (trunc (inspection_dt))
  5  ,constraint one_per_day unique (id, inspection_day)
  6  );

Table created.

The virtual column is defined on line 4 in the above CREATE statement.
If the table already exists, you can also add the Virtual Column with an ALTER statement.

SQL> alter table test add (
  2    inspection_date date
  3    generated always
  4    as
  5      (trunc (inspection_dt))
  6    virtual
  7  )
  8  /

Table altered.

To test the requirement, we simply insert date with SYSDATE into the table – wait a little bit (one second) – and insert again with SYSDATE.

SQL> insert into test (id, inspection_dt) values (1, sysdate)
  2  /

1 row created.

SQL> begin
  2     dbms_lock.sleep (1);
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> insert into test (id, inspection_dt) values (1, sysdate)
  2  /
insert into test (id, inspection_dt) values (1, sysdate)
*
ERROR at line 1:
ORA-00001: unique constraint (ALEX.ONE_PER_DAY) violated

Why the wait? Because I scripted this test (for the blog) SYSDATE has only second-granularity, so the records could be inserted in the same second. Which would have the same result as a unique constraint on the ID and INSPECTION_DT.

As you can see from the output in the above script, only one record is inserted into the table, the other attempt yields an exception.
Just to prove that the time component of the INSPECTION_DT column is recorded as per requirement:

SQL> select id
  2       , to_char (inspection_dt, 'dd-mm-yyyy hh24:mi:ss')
  3    from test
  4  /

        ID TO_CHAR(INSPECTION_
---------- -------------------
         1 24-08-2011 05:43:54

If you don’t have Oracle 11g, there is a way to implement this requirement with a Unique Function Based Index (will require at least Oracle 9i – I ran my scripts on an Oracle 10g Release 2).
We start of with the same table:

SQL> create table test
  2  (id         number
  3  ,inspection_dt date
  4  );

Table created.

And add the Unique Function Based Index on it.

SQL> create unique index one_per_day on test
  2  (id, trunc (inspection_dt))
  3  /

Index created.

The Function Based Index is based on the same expression we used earlier, the TRUNC (inspection_dt). Because this index is created as Unique, it will implement the requirement as stated.
Same testscript as before:

SQL> insert into test values (1, sysdate)
  2  /

1 row created.

SQL> begin
  2     dbms_lock.sleep (1);
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> insert into test values (1, sysdate)
  2  /
insert into test values (1, sysdate)
*
ERROR at line 1:
ORA-00001: unique constraint (ALEX.ONE_PER_DAY) violated

SQL> select id
  2       , to_char (inspection_dt, 'dd-mm-yyyy hh24:mi:ss')
  3    from test
  4  /

        ID TO_CHAR(INSPECTION_
---------- -------------------
         1 24-08-2011 05:43:52

Links

Oracle 9i docs
Oracle Base on Virtual Columns

UPDATE

My colleague Martijn Hoekstra just pointed out that Function Based Indexes were introduced in Oracle 8i, not Oracle 9i.
Oracle-Base on Function Based Indexes. Thank you Martijn for this correction.