Adding a unique constraint to the calculated column value

Question

I'm not quite sure how this phrase is, but it goes here ... We have a table structure such as:

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

Now for the data above ... I would like to have a restriction so that these 2 rows cannot exist together. Essentially, I want the table to be

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

I do not want rows with the same data created during X times of each other to be added to db, that is, in this case the restriction would be violated. The problem is that the specified restriction is not something that I can create.

Before asking, I know, I know ... why right? Well, I know that a certain scenario should not happen, but, alas, it is. At the moment, I need some measure of stopping, so I can buy some time to investigate the real issue. Let me know if you need more information ...

Answer 1

Yes, Oracle supports computed 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.

Answer 2

AFAIK, Oracle does not support computed columns such as SQL Server. You can simulate the functionality of a computed column using triggers.

Here are the steps for doing this.

• Add the column named CEILCalculationin the table.
  • , CEILCalculation CEIL(Timestamp/10000)*10000
  • (Unique for (Type, ClientId, CEILCalculation)

, BEFORE INSERT TRIGGER .

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