Oracle: DB *partial* constraint?

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.

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.

StackExchange Database Administrators Q#16503, answer score: 6