Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question
Are either TRANSACTION_TYPE or ORDER_ID nullable? – Jack Douglas Apr 14 '12 at 7:57
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
@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
up vote 6 down vote accepted

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.

share|improve this answer
I thought it should be something like: THEN (order_id, -1) ELSE (order_id, transaction_refernce) Or what you have plus another unique index for the 'REFUND' case. – ypercubeᵀᴹ Apr 14 '12 at 12:42
I haven't tested this myself but this seems a very feasible option :) – Vite Falcon Apr 15 '12 at 9:19
Sorry, I had first accepted this answer and then realized there was something wrong. ORDER_ID cannot be NULL. And having this this way would mean ORDER_ID will be NULL for refunds. A refund is attached to an order hence it should be there. Moreover I see more potential in @miracle173's answer. – Vite Falcon Apr 15 '12 at 9:26
@Vite: This answer does not mean that order_id will be nullable. But the index will be using NULL when the type is 'REFUND'. – ypercubeᵀᴹ Apr 15 '12 at 22:34
@ypercube: Thanks for clarifying that. It seems this answer is good enough to be accepted :). – Vite Falcon Apr 16 '12 at 9:07

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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