CODEWITHSUNDEEP
oracle-databaseconstraints
Revious
Revious

Reputation: 8156

Oracle: complex constraint involving other records

Is there any way to make oracle checks other records of a table by a constraint?

Let's take an example:

I've got a table called ENI_TRASC_VOCI_PWR_FATT and I want that every record having tvp_regione not null has a similar record having tvp_regione = null.

For similar record I need to check it has the same value on the TVP_CODICE_ASSOGGETAMEN colum.

Upvotes: 0

Views: 393

Answers (2)

David Aldridge
David Aldridge

Reputation: 52386

The only method I can think of is to use a fast commit on refresh materialised view, defined with a query something like:

select
  tvp_codice_assoggetamen,
  count(*)           rows_per_tca,
  count(tvp_regione) tvp_regione_per_tca
from
  eni_trasc_voci_pwr_fatt
group by
  tvp_codice_assoggetamen
/

Place regular check constraints on the MV table, such that tvp_regione_per_tca = 1 if rows_per_tca = 2 (your requirement is not quite clear to me).

That is generally the only safe way of implementing such a multi-row constraint in Oracle, short of locking the table for changes before modifying it and using code to check.

Upvotes: 3

A.B.Cade
A.B.Cade

Reputation: 16915

According to Oracle docs :

Conditions of check constraints cannot contain the following constructs:
Subqueries and scalar subquery expressions

So You'll probably have to use a trigger instead: 

create or replace trigger trg
before insert or update on ENI_TRASC_VOCI_PWR_FATT
for each row

begin
   -- do whatever queries you need - I didn't understand what you want
   if <some condition> then
     raise_application_error(-20000,'no good');
   end if;
end;

But be carefull ! a trigger is not like a constraint - think what will happen if two users update the table and so on ....

Upvotes: 1

Related Questions