Oracle/PLSQL Check-Unique Constraint / Index


Oracle constraints are used to enforce database integrity (enforce table rules and prevent data dependent. It can be used to enforce business rules.

Generally, there are 5 types of constraints (PUNCR) in the Oracle, ie:
* Primary Key (Type P)
* Unique (Type U)
* Not Null (Type N)
* Check (Type C)
* Foreign Key (Referential – Type R)

But I want 1 more type that use check and then unique, so i can be called Oracle Check-Unique Constraint. So I googling around and I did not find even one article that discusses it.

And then with the advice of my partner, Hendra Ong, by using the unique index this can be done.

So here, my problem
I want table TRD_COLLECTION have validation
if status = ‘3’ and pay_type =’C’ then must be use unique on these columns (ORG_ID, AGREEMENT_ID, PAY_TYPE)
and if status ‘3’ then no unique.

and the solution
CREATE UNIQUE INDEX TRD_COLLECTION_T01 ON TRD_COLLECTION
(CASE WHEN STATUS = ‘3’ AND PAY_TYPE = ‘C’ THEN
ORG_ID || AGREEMENT_ID || PAY_TYPE
ELSE
NULL
END)
TABLESPACE TMP_IDX

Any suggestions on that would be acceptable.

One Response

  1. There is obviously a lot to know about this. There are some good points here.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: