Oracle: DB partial constraint

constraintoracle

I am pretty new to Oracle and the database world. I am not sure if the title makes any sense at all, so I'll just go ahead and explain my situation.

I currently have a table at hand that records order transactions. The columns in this table are TRANSACTION_TYPE, ORDER_ID, QUANTITY, DATE, CREATED_BY, CREATION_DATE. The transaction types are 'PURCHASE' and 'REFUND'. Till now we only had full refunds because mostly the items bought were of single quantity. We're hoping to make it multiple in which case we will possibly have to perform multiple refunds.

The tables unique-key constraint is a combination of TRANSACTION_TYPE, ORDER_ID. I can break this constraint to make it possible to do multiple refunds by expanding the combination to TRANSACTION_TYPE, ORDER_ID, TRANSACTION_REFERENCE (this reference comes from the service that does the actual purchase or refund). But there's the thing, this means that I can do multiple refunds on the same order and the same is possible if it is a purchase. I would like to create a constraint such that the combination of 'PURCHASE' + ORDER_ID is unique in the table so that only a single purchase is possible for a given ORDER_ID by constraint.

I tried searching for this possibility and even tried it in this site. I might be using the wrong terms here. But if anyone here has an answer for this, I would LOVE to hear it out.

Any help to even nudge me to the right direction would be much appreciated.

Best Answer

You can create a function-based index. If the table is named TRANSACTION

CREATE UNIQUE INDEX only_one_purchase
    ON transaction( (case when transaction_type = 'PURCHASE'
                          then order_id
                          else null
                      end) );

This leverages the fact that Oracle doesn't store completely NULL rows in the index so only the order_id values that are part of PURCHASE transactions will be stored in the index.

Related Solutions

Sql-server – Any way around unique index 16 column max

Add a persisted computed column that combines the 18 keys, then create an unique index on the computed column:

alter table t add all_keys as c1+c2+c3+...+c18 persisted;
create unique index i18 on t (all_keys);

See Creating Indexes on Computed Columns.

Another approach is to create an indexed view:

create view v 
with schemabinding
as select c1+c2+c3+...+c18 as all_keys
from dbo.t;

create unique clustered index c18 on v(all_keys);

See Creating Indexed Views.

Both approaches allow for a partial key aggregate: aggregate c1+c2+c3 as k1, c4+c5+c6 as k2 etc. then index/create indexed view on (k1, k2, ...). Thia could be beneficial for range scans (index can be used for search on c1+c2+c3.

Of course, all + operation in my example are string aggregation, the actual operator to use depends on the types of all those columns (ie. you may have to use explicit casts).

PS. As unique constraints are enforced by an unique index, any restriction on unique indexes will apply to unique constraints as well:

create table t (
    c1 char(3), c2 char(3), c3 char(3), c4 char(3),
    c5 char(3), c6 char(3), c7 char(3), c8 char(3),
    c9 char(3), c10 char(3), c11 char(3), c12 char(3),
    c13 char(3), c14 char(3), c15 char(3), c16 char(3),
    c17 char(3), c18 char(3), c19 char(3), c20 char(3),
    constraint unq unique
      (c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16,c17,c18));
go  


Msg 1904, Level 16, State 1, Line 3
The index '' on table 't' has 18 column names in index key list. 
The maximum limit for index or statistics key column list is 16.
Msg 1750, Level 16, State 0, Line 3
Could not create constraint. See previous errors.

However, creating the constraint on a persisted computed column works:

create table t (
    c1 char(3), c2 char(3), c3 char(3), c4 char(3),
    c5 char(3), c6 char(3), c7 char(3), c8 char(3),
    c9 char(3), c10 char(3), c11 char(3), c12 char(3),
    c13 char(3), c14 char(3), c15 char(3), c16 char(3),
    c17 char(3), c18 char(3), c19 char(3), c20 char(3),
    all_c as 
        c1+c2+c3+c4+c5+c6+c7+c8+c9+c10+c11+
        c12+c13+c14+c15+c16+c17+c18 
        persisted
        constraint unq unique (all_c));
go

Obviously, the persisted column consumes the space on disk so the approach may be bad for a very large table. The indexed view approach does not have this problem, it only consumes the space for the index, not the space for the computed column and index.

Sql-server – UDF in check constraint — downside

You can store A and B columns in a separate table. Make sure your table has a primary key on A, and a unique constraint on (A,B) Refer to (A,B) from your table. The primary key on A will guarantee only one B per A. The foreign key without ON UPDATE CASCADE will make sure B does not change as long as the row is referred from the child table.

Best Answer

Related Solutions

Sql-server – Any way around unique index 16 column max

Sql-server – UDF in check constraint — downside

Related Question