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
Any suggestions on that would be acceptable.