Adding a unique constraint on calculated value of a column

Question

Hi all, I'm not exactly sure how to phrase this, but here goes... We have a table structure like the following:

Id   |   Timestamp    | Type  | Clientid   | ..others..
001  |   1234567890   | TYPE1 | CL1234567  |.....    
002  |   1234561890   | TYPE1 | CL1234567  |.....    

Now for the data given above... I would like to have a constraint so that those 2 rows could not exist together. Essentially, I want the table to be

Unique for (Type, ClientId, CEIL(Timestamp/10000)*10000)

I don't want rows with the same data created within X time of each other to be added to the db, i.e would like a constraint violation in this case. The problem is that, the above constraint is not something I can actually create.

Before you ask, I know, I know.... why right? Well I know a certain scenario should not be happening, but alas it is. I need a sort of stop gap measure for now, so I can buy some time to investigate the actual matter. Let me know if you need additional info...

Answer 1

AFAIK, Oracle does not support computed columns like SQL Server does. You can mimic the functionality of a computed column using Triggers.

Here are the steps for this

1. Add a column called CEILCalculation to your table.
   • On your table, put a trigger will update CEILCalculation with the value from CEIL(Timestamp/10000)*10000
   • Create a Unique Index on the three columns (Unique for (Type, ClientId, CEILCalculation)

If you do not want to modify the table structure, you can put a BEFORE INSERT TRIGGER on the table and check for validity over there.

http://www.techonthenet.com/oracle/triggers/before_insert.php

Answer 2

Yes, Oracle supports calculated columns:

SQL> alter table test add calc_column as (trunc(timestamp/10000));

Table altered.

SQL> alter table test
     add constraint test_uniq
     unique (type, clientid, calc_column);

Table altered.

should do what you want.