Need suggestion on how to force referential integrity

subratarec · Posted: Wed Apr 25, 2018 9:22 am

Hi,

I need suggestion on below scenario.

We are trying to create 4 new tables. Details on the tables given below..

1. D1 table
2. D2 table
3. A0 table
4. T0 table

Now the data will be populated into these table from one file which we will receive from outside. The record will signify this..

D1 and D2 are 2 different main detail record type and it will have it's corresponding A0 and T0 records. Means data will come like D1/A0/T0 or D2/A0/T0. There is unique key (25 char) which will connect D1 or D2 with their respective A0 and T0.

Now we are thinking to insert all D1 only records to D1 table and D2 only records to D2 table. But all A0 and T0 (belongs to both D1 and D2) will got to A0 and T0 table.

Now I was thinking how to create a way that..

1. when D1 data will be deleted from D1 table then D1's corresponding (I mean same unique key which is there in A0 and T0) A0 and T0 records also should automatically gets deleted from A0 and T0 tables

2. when D2 data will be deleted from D2 table then D2's corresponding (I mean same unique key which is there in A0 and T0) A0 and T0 records also should automatically gets deleted from A0 and T0 tables

Could you please help me to find which would be the best way to implement the above concept (can we use 'ON DELETE CASCADE' or something like DB2 Trigger??)

NOTE - I was thinking to introduce Foreign key but as A0 and T0 tables are common to both D1 and D2 can we have that ?

Thanks in advance

guptae · Posted: Wed Apr 25, 2018 10:03 am

Hello there,

Yes your approach is correct. Lets take it like this

D1 primary key dpk1
D2 Primary key dpk2

Now you need to define A0 and T0 LIKE

A0 should have column dpk1 and dpk2
Similarly T0 should have dpk1 and dpk2

I am assuming there is no relationship between A0 AND T0

now you need to create 4 RIs like below

ALTER TABLE A0 FOREIGN KEY A0_FK1
(DPK1)
REFERENCES D1
(DPK1)
ON DELETE CASCADE ENFORCED ;

ALTER TABLE A0 FOREIGN KEY A0_FK2
(DPK2)
REFERENCES D2
(DPK2)
ON DELETE CASCADE ENFORCED ;

ALTER TABLE T0 FOREIGN KEY T0_FK1
(DPK1)
REFERENCES D1
(DPK1)
ON DELETE CASCADE ENFORCED ;

ALTER TABLE T0 FOREIGN KEY T0_FK2
(DPK2)
REFERENCES D2
(DPK2)
ON DELETE CASCADE ENFORCED ;

Please let me know if you need any other details

subratarec · Posted: Thu Apr 26, 2018 1:28 pm

Hi Guptae,

Thanks for quick reply and I apologize for delay in my reply. Sure I will try below approach. I will come back to you if I face any issue or if there is minor requirement change (as you know client does changes abruptly)..

Thanks

Rohit Umarjikar · Posted: Fri Apr 27, 2018 7:38 am

How come you don’t have Data Modeller in your firm? This questions ain’t suppose to be discussed here and made decisions.

subratarec · Posted: Sat Apr 28, 2018 2:15 pm

Hi Rohit,

Your question is absolutely correct and the answer is 'yes' they have!. But the main issue is currently we were informed that our DBAs are very short handed so for our new project what we are doing is we are creating what all we can and understand from our end.

I wanted to make sure if my idea towards above subject is correct. We have create our compete data dictionary on our own in Excel and gave that to our DBA I guess they will pass the date to Powerbuilder tool to generate table design.

Rohit Umarjikar · Posted: Sat Apr 28, 2018 8:45 pm

DBAs comes in a play after Data Modeler instructs them with DDL to create. So they are different groups of people.

Anyways , why do you need 4 tables ? Why not D1D2 goes into one table and AO, TO goes to one table? Just add a identifier column in each of the table to know what is what entry, because imagin, if you get new D3,D4 and so on then you can’t keep creating new tables right? Just change the identifier type and insert into one D table.
Moreover use triggers to achieve what you want.

subratarec · Posted: Tue May 08, 2018 1:23 pm

Hi Rohit,

I do agree with your suggestion and I suggested them to add both D1 and D2 into one table (if they need they can simply query with record types along with other details in where clause) but the issue is both these types of records will be needed by two different streams (outside to our application). So they decided to keep them separate so they two different streams can make use those.

We are expecting D3 rec type soon

I used Trigger to achieve the result.

Again Thanks! for replying. I always need all your suggestions.

Thanks

Rohit Umarjikar · Posted: Tue May 08, 2018 7:26 pm

You are Welcome!!