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