CODEWITHSUNDEEP
sqloracle-databaseconstraintsunique-constraintcheck-constraint
muellae
muellae

Reputation: 97

Oracle Constraints: mix unique and check constraints

I'm facing the following problem: I would like to create a mix of unique and check constraints.

It's probably more business logic than the responsibility of the DB, but still.

I'm having a Table with the following structure:

desc INSURANCE;

Name              Null     Typ           
----------------- -------- ------------- 
ID                NOT NULL NUMBER        
CUST_ID           NOT NULL NUMBER        
CONTRACT_TYPE     NOT NULL NUMBER
FROM_DATE         NOT NULL DATE          
TO_DATE           NOT NULL DATE

The design itself may be wrong, it's just an example.

I would like to have a constraint in this manner:

alter table INSURANCE 
add constraint unique(CUST_ID, CONTRACT_TYPE)
and check (FROM_DATE not between FROM_DATE and TO_DATE)
and check (TO_DATE   not between FROM_DATE and TO_DATE) ;

In words: It's allowed to have two insurances with the same CUST_ID and CONTRACT_TYPE, as long as the periods are not intersecting each other.

Is there any solution for this kind of problem?

Thanks for any hint!

Cheers muellae

Upvotes: 3

Views: 251

Answers (2)

Mark Wagoner
Mark Wagoner

Reputation: 1769

You could use a 'before' trigger. In the trigger code perform the check you describe and if you find any existing rows then raise an exception.

Upvotes: 1

Steve Pettifer
Steve Pettifer

Reputation: 2043

You will probably want a function based constraint. I won't go into the details here because there are a ton of resources already out there - simply Google 'Oracle Function Based Constraint' and you'll get plenty. I wouldn't want to speculate on an performance impacts however - I think your statement that it should probably be business logic is 100% correct. Don't throw crap data at your database, reject it at source.

Upvotes: 1

Related Questions