topic Re: Foreign key dependency checking in Application Development and Automation Discussions

Foreign key dependency checking

former_member457651 — Mon, 03 Nov 2008 04:28:51 GMT

Dear all:

I have a question about foreign key checking.

Let's say I have two Z table, one is master table and one is transaction table.

The master table contains a primary key named CustNo.

The transaction table have two primary keys named CustNo and OrderNo.

The CustNo has foreign key (mandt + CustNo) point to master table, with setting

"Key fields/candidates 1:N"

I expected that, delete record in master table should check if there is any dependency in the transaction table,

but I am wrong. I am able to delete anything in master table even there are records in transaction table using the same CustNo.

My question is, how can I make the dependency check?

Thanks in advance.

Edited by: Hung Kai, Michael Cheng on Nov 3, 2008 5:29 AM

Re: Foreign key dependency checking

Former Member — Mon, 03 Nov 2008 09:41:38 GMT

Hello,

For this u need to change the cardinality.

Below are the list of cardinalities and their functionality.Chack it and use the proper one.

The left side (n) of the cardinality is defined as follows:

n=1: There is exactly one record assigned to the check table for each record of the foreign key table.

n=C: The foreign key table may contain records which do not correspond to any record of the check table because the foreign key field is empty. This can occur for example if the field of the foreign key table is optional, in which case it does not have to be filled.

The right side (m) of the cardinality is defined as follows:

m=1: There is exactly one dependent record for each record of the check table.

m=C: There is at most one dependent record for each record of the check table.

m=N: There is at least one dependent record for each record of the check table.

m=CN: There may be any number of dependent records for each record of the check table.

Shafi

Re: Foreign key dependency checking

Former Member — Mon, 03 Nov 2008 10:44:22 GMT

hello Hung Kai,

Yah you ve set the cardinality perfectly. But one thing you remember that

you can delete record in master table : it wont check in the transaction table.

but while you delete record in transaction table : it will check record existance in master table.

Re: Foreign key dependency checking

former_member457651 — Fri, 14 Nov 2008 02:05:33 GMT

So, the conclusion is, in order to ensure that there is no dependent records in the master table, I need to check it myself??

It is not that difficult, but is kind of strange

Re: Foreign key dependency checking

alejandro_bindi — Fri, 14 Nov 2008 03:32:30 GMT

Yes, that's the awful truth. The FK relationships you can define play a role in Dynpros, for example, a message can be issued automatically in SM30 if you enter a key on the transactional table which does not exist in the master table.

But you cannot set restrictions, cascading deletion or something like that. Anything you code in Open SQL (INSERTs, DELETEs, etc) can break the DB integrity. So you have to do all the checks by yourself previously.