0

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?

OSEMA TOUATI
  • 327
  • 2
  • 13
  • Can you please give us the table structure and some example, how are the two tables related , do they have column or columns on which you can join and check the data ? Is there a driver table ? what happens when you add a row 1st time in a table , how do you want to handle that ? – Himanshu Kandpal Sep 02 '21 at 23:37
  • I added more details, what i want is every time i add a row in data, if this row has a paq associated to it then i have to make sure that E_FLAG of data and paq have the same value. – OSEMA TOUATI Sep 02 '21 at 23:56

1 Answers1

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 );
Justin Cave
  • 227,342
  • 24
  • 367
  • 384
  • Yes I agree with this, I'm working in a project where i actually had to denormalize. the question that i asked is very simplified and addresses the core of my problem. – OSEMA TOUATI Sep 03 '21 at 00:34