oracle-databaseconstraintsdatabase-integrity

Integrity constraints to make sur two columns contain same values for two related tables


I have two tables in my oracle database.

+--------+                     +---------+    
+ data   + --1..0-------0..n --+   PAQ   +  
+--------+                     +---------+

PAQ has many data.

Here is an example of how they're made:

create table data {
data_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
value number,
data_date date,
E_FLAG char,
paq_id number,
primary key(data_id),
foreign key(paq_id) references paq.paq_id
}

create table paq {
paq_id number generated by default as identity,
E_FLAG char,
...
primary key(paq_id)
}

there is a column which is duplicated called E_FLAG.

I want to insure the following:
If data1 belongs to paq1 then data1.E_FLAG == paq1.E_FLAG. using integrity constraints in oracle.

I this possible?


Solution

  • The right answer would be to normalize the data model and not duplicate the column in the data table.

    That said, if you create a unique index on paq

    create unique index uniq_paq_flag
        on paq( paq_id, e_flag );
    

    You can then create a foreign key constraint on that combination that ensures that the e_flag value matches

    alter table data
      add constraint fk_match_flag 
        foreign key( paq_id, e_flag ) 
        references paq( paq_id, e_flag );