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.

  • Are either TRANSACTION_TYPE or ORDER_ID nullable? – Jack says try topanswers.xyz Apr 14 '12 at 7:57
  • 3
    a way to implement this is already given by @Justin Cave but nevertheless squeezing different attibutes of related entities in the same columns of the same table seems bad design to me. You have a one to many relation between the order entity and the refund entity. So I think you should have a table "refund" with a foreign key referencing the "order" table or something similiar. Then you need only simple primary key, unique and reference contraints. – miracle173 Apr 14 '12 at 11:25
  • 1
    @miracle173: That sounds like a better option than cramming in more constratints in the table. If you could answer that below, I could accept your answer :) – Vite Falcon Apr 15 '12 at 9:27

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.