09-28-2016 10:13 AM
i'm creating an sql procedure for deleting old data on common data model. In particular, the request is just for some CELL_PACKAGE_SK of a communication.
the deleting table order is the following:
It works if a communication has 1 cell_package_sk, but if it has 2 or more cell_package_sk the procedure generates an oracle error for the foreign key violation on CI_CELL_PACKAGE table:
Error SQL: ORA-02292: integrity constraint (CAMPAIGN.CELL_PKG_FK1) violated - child record found
Do you have some suggestions?
09-29-2016 04:07 AM
I don't have access to this data model in particular.
But the basic principle is to do deletes in the "child" tables first, the tables that have FK:s.
And then the tables with PK:s.
09-29-2016 09:33 AM
09-30-2016 09:56 AM
The scenario is the following:
RUN1 --> CAMPAIGN_CD=CAMP1111 COMMUNICATION_CD=COMM2111 CELL_PACKAGE_SK=1110 COMMUNICATION_SK=2111
RUN2 --> CAMPAIGN_CD=CAMP1111 COMMUNICATION_CD=COMM2111 CELL_PACKAGE_SK=1120 COMMUNICATION_SK=2112
SUBJECT PER RUN:
RUN1: 3000 SUBJECTS FOR CELL_PACKAGE_SK=1110
RUN2: 5000 SUBJECTS FOR CELL_PACKAGE_SK=1120
Aim: deleting CELL_PACKAGE_sK=1110 and its subjects from common data model tables.
10-04-2016 02:45 AM - edited 10-04-2016 02:47 AM
Like @LinusH I don't know the specific data model - but "extrapolating" from the table names I'd assume ..._udf stand for "user defined field" and columns in these tables seem to have a foreign key constraint on the parent table CI_CELL_PACKAGE.
If my assumption is correct then not every record in CI_CELL_PACKAGE has a child record in the ...udf tables.
- "It works if a communication has 1 cell_package_sk": You've chosen test cases with no child records in one of the ...udf tables, so it's not about one or several sk's but about your test cases.
- "but if it has 2 or more cell_package_sk the procedure generates an oracle error": here you've got test cases with child records
Most likely solution
You need first to delete the child records in the ...udf tables before you can delete the parent in CI_CELL_PACKAGE. That's what the integrity constraint checks for.
Linus asked you for the constraint definition as this one tells you in which table you need to delete records first.
10-06-2016 09:46 AM
UDF tables contain custom details of a communication node. In my test cases both have records on them (Key: COMMUNICATION_SK).
Probably the problem is the CI_PACKAGE table. For each campaign there 's 1 package_sk for all cell_package_sk genereted by multiple campaing's run. So if I have a campaign with 1 cell_package_sk no problem , but it's impossible delete the package_sk from CI_PACKAGE if there are other cell_package_sk with the same package_sk.
the problem doesn't occour if I delete all cell_package_sk.