Managing Common Data Model

[teresa_abbate]

Hi,

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:

CI_CONTACT_HISTORY

CI_RESPONSE_HISTORY

CI_CELL_PACKAGE

CI_COMMUNICATION_CHAR_UDF

CI_COMMUNICATION_DATE_UDF

CI_COMMUNICATION_NUM_UDF

CI_COMMUNICATION_EXT

CI_COMMUNICATION

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?

Thanks,

Teresa

[LinusH]

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.

[pcapazzi]

Can you provide more detail about the criteria you are using to delete the
"old" data?

A scenario where I think you're having trouble is this: You're deleting
subjects from a campaign with a contact_dt of 09/29/2016 that are attached
to one cell_package_sk. There may be subjects from the same campaign and
communication that have a contact date of 10/1. Since those agents exist
the cell_package_sk would not be purged. The communication would then have
a child cell_package_sk that still exists.

If that's the case then your script should delete records from CDM where
the campaign has no subjects in it's contact history that have not yet made
the threshold to be purged. ​


##- Please type your reply above this line. Simple formatting, no
attachments. -##

[teresa_abbate]

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.

[pcapazzi]

What is the definition of the constraint?

[Patrick]

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.

Assumptions

- "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.

[teresa_abbate]

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.