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

Question

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?

Answer 1

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 );