Need help n creating Adapted Foreign Key

[Former Member]

Can anyone please tell how to create Adapted Foreign Key?

I have used 3 tables. The details are -

Table Type -> Check Table

Table Name -> YCT

Fields -> Country, Region

Values -> 1 entry -> INDIA, MUMBAI

Table Type -> Foreign Key table

Table Name -> YFKT

Fields -> Country

Table Type -> Another Table

Table Name -> YADFKT

Fields -> Region

Values -> 1 entry -> MUMBAI

Since this is an adapted Foreign Key, So the relationships are -:

YCT-Country = YFKT-Country

AND

YCT-REGION = YADFKT-Region

That means i want Valid "Country" should be present in check table (YCT) and for that a valid "Region" must be there in "YADFKT".

But when I am trying to enter data "INDIA" in YFKT then it is saying "Entry is not present in Check Table".

Why this error is coming ?

[Former Member]

Can anyone please tell how to create Adapted Foreign Key?

I have used 3 tables. The details are -

Table Type -> Check Table

Table Name -> YCT

Fields -> Country, Region

Values -> 1 entry -> INDIA, MUMBAI

Table Type -> Foreign Key table

Table Name -> YFKT

Fields -> Country

Table Type -> Another Table

Table Name -> YADFKT

Fields -> Region

Values -> 1 entry -> MUMBAI

Since this is an adapted Foreign Key, So the relationships are -:

YCT-Country = YFKT-Country

AND

YCT-REGION = YADFKT-Region

That means i want Valid "Country" should be present in check table (YCT) and for that a valid "Region" must be there in "YADFKT".

But when I am trying to enter data "INDIA" in YFKT then it is saying "Entry is not present in Check Table".

Why this error is coming ?

[Former Member]

Adapted foreign keys: If all the fields of a compound foreign key are not from same table it is called as an adapted foreign key.

I don't see this as a case of Adapted Foriegn keys

YFKT-COUNTRY = YCT-COUNTRY

YCT-REGION = YADFKT-REGION

bold representing the foreign key.

Edited by: Ncvajja on Feb 8, 2012 4:35 AM

[Former Member]

Hi Ncvajja,

Could you please explain with an example. I will be grateful to you.

What do you mean by this "

YFKT-COUNTRY = YCT-COUNTRY

YCT-REGION = YADFKT-REGION

bold representing the foreign key.

[Former Member]

Can anyone please help me to create Adapted Foreign Key?

"Foreign key fields of a compound foreign key do not all have to reside in the same table. If they do not, this is known as an adapted foreign key."

I have created 3 tables as follows.

1. Check table - zkicktb

Fields - State, Country

Values - 1. New Delhi, India

2. Karnataka, India

3. California, USA

4. New York, USA

2. Foreign table - zkiemptb

Fields - primary key: empno

compound foreign key: state (check field of compound foreign key) and country(belong to different table zkicc)

empname

3. Different table(consists of the field "country" of compound foreign key) - zkicc

Fields - country

How to create an Adapted Foreign Key?

Edited by: prince_1982 on Feb 8, 2012 2:48 PM

[Former Member]

This would be long...

Table Type -> Check Table ->YCT
Fields -> Country, Region
Values -> 1 entry -> INDIA, MUMBAI
Table Type -> Foreign Key table -> YFKT
Fields -> Country
values-> India Thats your entry 
Table Type -> Another check Table -> YADFKT
Fields -> Region
Values -> 1 entry -> MUMBAI
YCT-Country = YFKT-Country AND YCT-REGION = YADFKT-Region
That means i want Valid "Country" should be present in check table (YCT) and for that a valid "Region" must be there in "YADFKT".

This is different from the example quoted now....or maybe just confusing

1. Check table - zkicktb 
Fields - State, Country
Values - 1. New Delhi, India
               2. Karnataka, India	
               3. California, USA
               4. New York, USA
2. Foreign table - zkiemptb
Fields - primary key: empno compound foreign key: state (check field of compound foreign key) and country(belong to different table zkicc)
3. Different table(consists of the field "country" of compound foreign key) - zkicc
Fields - country

Your solution would be

In ZKIEMPTB

for STATE give foreign key generate proposal for ZKICKTB - STATE

for COUNTRY give foreign key generate proposal for ZKICC - COUNTRY

But this to me sounds a bit odd of we are dealing with the example in question...

Why not create a compound key b/w ZKIEMPTB and ZKICKTB for both COUNTRY and CITY.....and ZKICKTB-COUNTRY and ZKICC-COUNTRY. You obviously wont have California, US and California, DE

Edited by: Ncvajja on Feb 8, 2012 6:53 PM

[Former Member]

First of all, thanks a lot for your response and time.

The definition of adapted foreign key says "Foreign key fields of a compound foreign key do not all have to reside in the same table. If they do not, this is known as an adapted foreign key."

So that means there should be more than one table. what type of tables should it be? does this mean both or all tables should be foreign key tables or check tables?

-

---

Table Type -> Check Table ->YCT

Fields -> Country, Region

Values -> 1 entry -> INDIA, MUMBAI

Table Type -> Foreign Key table -> YFKT

Fields -> Country

values-> India Thats your entry

Table Type -> Another check Table -> YADFKT

Fields -> Region

Values -> 1 entry -> MUMBAI

YCT-Country = YFKT-Country AND YCT-REGION = YADFKT-Region

That means i want Valid "Country" should be present in check table (YCT) and for that a valid "Region" must be there in "YADFKT".

-

---

could you please tell me which one is compound foreign key in the above example? which is adapted foreign key?

I did not understand this "Why not create a compound key b/w ZKIEMPTB and ZKICKTB for both COUNTRY and CITY.....and ZKICKTB-COUNTRY and ZKICC-COUNTRY. You obviously wont have California, US and California, DE"

What do you mean by DE? Is it Data Element?

I'm sorry for asking many things. I'm a novice. I searched a lot for information on Adaptable foreign key but could not find. If it is difficult to explain with my examples please explain with your convenience.

Thank you.

[Former Member]

I was referring "SAP - Sams Teach Yourself ABAP 4 in 21 Days"

Could you please check this [http://www.sap-img.com/abap/compound-constant-and-adapted-foreign-keys.htm]

explain?

The definition of Adaptable foreign key says "Foreign key fields of a compound foreign key do not all have to reside in the same table. If they do not, this is known as an adapted foreign key."

So I created the check table(zkicktb) for validating the compound foreign key consisting f zkiemptb-state(check field) and zkicc-country(as definition says this field belongs to different table)

1. Check table - zkicktb

Fields - State, Country

Values - 1. New Delhi, India

2. California, USA

2. Foreign table - zkiemptb

Fields - primary key: empno compound foreign key: state (check field of compound foreign key) and country(belong to different table zkicc)

3. Different table(consists of the field "country" of compound foreign key) - zkicc

Fields - country

Values 1. India

2.USA

When I enter "New Delhi" iin zkiemptb-state it says "New Delhi" is not in check table zkicktb"

[Former Member]

Let me try this with an example..

ZMAINTABLE

MANDT type MANDT. as key field

ZKUNN type KUNNR. as key field

ZCNTY type LAND1.

ZREGIO type REGIO.

ZCHKCMPDKEY_

MANDT type MANDT. as key field

ZCNTY type LAND1. as key field

ZREGIO type REGIO. as key field

Values

800 DE CK

800 US AZ

800 US AR

800 US CA

Now in ZMAINTABLE select ZCNTY and ZREGIO and generate proposal for both the fields from table ZCHK_CMPDKEY. Now if you enter 800 DE CA it will throw as error as such a combination doesnt exist

But say if you tables were to be

ZCHKCTY_

MANDT type MANDT. as key field

ZCNTY type LAND!. as key field

Values

800 DE

800 US

ZCHKREGI_

MANDT type MANDT. as key field

ZREGIO type REGIO. as key field

Values

800 CA

800 AR

800 AZ

Now we have foriegn key defined as ZMAINTABLE-ZCNTY = ZCHK_CTY-ZCNTY and ZMAINTABLE-ZREGIO= ZCHK_REGI-ZREGIO; *then the values 800 DE CA for ZMAINTABLE will be allowed

First example is COMPOUND Foreign key and second example is ADAPTED foreign key

Edited by: Ncvajja on Feb 9, 2012 5:16 AM

[Former Member]

Thanks again. I understand the first example which is compound foreign key.

Adapted foreign keys: If all the fields of a compound foreign key are not from same table it is called as an adapted foreign key.

This means Adapted foreign key itself is compound foreign key.

In second example,

ZMAINTABLE

MANDT type MANDT. as key field

ZKUNN type KUNNR. as key field

ZCNTY type LAND1.

ZREGIO type REGIO.

ZCHKCTY_

MANDT type MANDT. as key field

ZCNTY type LAND!. as key field

Values

800 DE

800 US

ZCHKREGI_

MANDT type MANDT. as key field

ZREGIO type REGIO. as key field

Values

800 CA

800 AR

800 AZ

If we define foriegn key as ZMAINTABLE-ZCNTY = ZCHK_CTY-ZCNTY and ZMAINTABLE-ZREGIO= ZCHK_REGI-ZREGIO how it would be adapted key? because both ZMAINTABLE-ZCNTY and ZMAINTABLE-ZREGIO foreign keys are independent foreign keys not compound keys.

A Compound Foreign Key is a foreign key that composed of 2 or more fields. In a Compound Foreign Key, the Check Field is the field where it is defined as the foreign key. Hence, a filled Check Field triggers the check against the Check Table. Other filled fields within the Compound Foreign Key will not trigger the validation.

In adapted foreign key all the fields of a compound foreign key should not belong to the same table. In this example both ZMAINTABLE-ZCNTY and ZMAINTABLE-ZREGIO foreign keys belong to the same table that is ZMAINTABLE

[Former Member]

Thanks again. I understand the first example which is compound foreign key.

Adapted foreign keys: If all the fields of a compound foreign key are not from same table it is called as an adapted foreign key.

This means Adapted foreign key itself is compound foreign key.

In second example,

ZMAINTABLE

MANDT type MANDT. as key field

ZKUNN type KUNNR. as key field

ZCNTY type LAND1.

ZREGIO type REGIO.

ZCHKCTY_

MANDT type MANDT. as key field

ZCNTY type LAND!. as key field

Values

800 DE

800 US

ZCHKREGI_

MANDT type MANDT. as key field

ZREGIO type REGIO. as key field

Values

800 CA

800 AR

800 AZ

If we define foriegn key as ZMAINTABLE-ZCNTY = ZCHK_CTY-ZCNTY and ZMAINTABLE-ZREGIO= ZCHK_REGI-ZREGIO how it would be adapted key? because both ZMAINTABLE-ZCNTY and ZMAINTABLE-ZREGIO foreign keys are independent foreign keys not compound keys.

A Compound Foreign Key is a foreign key that composed of 2 or more fields. In a Compound Foreign Key, the Check Field is the field where it is defined as the foreign key. Hence, a filled Check Field triggers the check against the Check Table. Other filled fields within the Compound Foreign Key will not trigger the validation.

In adapted foreign key all the fields of a compound foreign key should not belong to the same table. In this example both ZMAINTABLE-ZCNTY and ZMAINTABLE-ZREGIO foreign keys belong to the same table that is ZMAINTABLE

Please see the following link

[https://picasaweb.google.com/108586597501023552308/February9201202#5707050984585861058]

Here the check table is same for all fields that is ZTXT005S and there is 2 different foreign key table that is ZTXLFA1 and ZTXLFA1CC

ZTXT005S-MANDT = ZTXLFA1-MANDT

ZTXT005S-LAND1 = ZTXLFA1CC-LAND1 (HERE THE FIELD LAND1 BELONGS TO DIFFERENT TABLE ZTXLFA1CC)

ZTXT005S-REGIO = ZTXLFA1-REGIO

COMPOUND KEY CONSISTS OF 2 FIELDS ZTXLFA1-REGIO AND ZTXLFA1CC-LAND1

The validation on the ZTXLFA1-REGIO field(CHECK FIELD) have to check to see whether the combination of REGIO exists in ZTXT005S and whether LAND1 existS in any row of ZTXLFA1CC.

Please explain.

There are many people who are experienced don't want to help. It is so sad.

Edited by: prince_1982 on Feb 9, 2012 9:35 AM

[Former Member]

Nothing sad about it brother....they've got their own jobs to do.

Yeah 21 days is a good book and i think it is explained clearly.

Since you have understood Compound foreign key...I'll make adapted foriegn key simple and short...

*Disregard that example earlier....it was ill conceived

Main table has REGION and COUNTRY CODE as in same case as above.

On foriegn key of MAINTABLE-REGION we give CHECK_TBL1 as checktable

CHECK_TBL1 as REGION and COUNTRY CODE.

Now in the proposal generated CHECK_TBL1-REGION = MAINTABLE-REGION (and will be input disabled)

But we now restrict the CHECKTBL1-COUNTRY_CODE for this proposal only by

CHECK_TBL1-COUNTRY_CODE = CHECK_TBL2-COUNTRY_CODE.

Hope that's clear

[Former Member]

Yes, everybody has got their own jobs but nothing will happen if they share their knowledge and spend little time. Once they were also novice and still here for knowledge. I am grateful to you because you are an exception.

Please note this Adapted foreign keys: If all the fields of a compound foreign key are not from same table it is called as an adapted foreign key.

The condition is that all the fields of a compound key should not belong to the same table.

In your example both LAND1 and REGIO belong to the same table then how it would be an adapted foreign key? this is my question.

Could you please tell me which is compound foreign key and adapted foreign key here.

What do you mean by

"But we now restrict the CHECKTBL1-COUNTRY_CODE for this proposal only by

CHECK_TBL1-COUNTRY_CODE = CHECK_TBL2-COUNTRY_CODE."

How many check tables do exist?

Please tell me whether you check the following link

[https://picasaweb.google.com/108586597501023552308/February9201202#5707050984585861058]

Edited by: prince_1982 on Feb 9, 2012 4:43 PM

[Former Member]

Summing from the posts

This is compound foreign key

Let me try this with an example..
ZMAINTABLE
MANDT type MANDT. as key field
ZKUNN type KUNNR. as key field
ZCNTY type LAND1.
ZREGIO type REGIO.
ZCHKCMPDKEY_
MANDT type MANDT. as key field
ZCNTY type LAND1. as key field
ZREGIO type REGIO. as key field
Values
800 DE CK
800 US AZ
800 US AR
800 US CA
Now in ZMAINTABLE select ZCNTY and ZREGIO and generate proposal for  one fields from table ZMAINTABLE and ZCHK_CMPDKEY. 

Example in previous post is ADAPTED key.

What do you mean by 
"But we now restrict the CHECKTBL1-COUNTRY_CODE for this proposal only by
CHECK_TBL1-COUNTRY_CODE = CHECK_TBL2-COUNTRY_CODE."
How many check tables do exist?

One main table and ....you can say two check tables (Similar to A checks B and B checks C)

[Former Member]

This is the link you are referring to right

[http://www.sapnet.ru/abap21day/ch04/ch04.htm#CompoundForeignKeys]

It will be simpler if you visualize this similar to...say how you worked on set theory.

[Former Member]

Please note this Adapted foreign keys: If all the fields of a compound foreign key are not from same table it is called as an adapted foreign key.

The condition is that all the fields of a compound key should not belong to the same table.

In your example both LAND1 and REGIO belong to the same table then how it would be an adapted foreign key? this is my question.

In this link [http://www.sapnet.ru/abap21day/ch04/ch04.htm#CompoundForeignKeys]

the check table is same for all fields that is ZTXT005S and there is 2 different foreign key table that is ZTXLFA1 and ZTXLFA1CC

ZTXT005S-MANDT = ZTXLFA1-MANDT

ZTXT005S-LAND1 = ZTXLFA1CC-LAND1 (HERE THE FIELD LAND1 BELONGS TO DIFFERENT TABLE ZTXLFA1CC)

ZTXT005S-REGIO = ZTXLFA1-REGIO

COMPOUND KEY CONSISTS OF 2 FIELDS ZTXLFA1-REGIO AND ZTXLFA1CC-LAND1

The validation on the ZTXLFA1-REGIO field(CHECK FIELD) have to check to see whether the combination of REGIO exists in ZTXT005S and whether LAND1 existS in any row of ZTXLFA1CC.

Edited by: prince_1982 on Feb 9, 2012 5:49 PM

[Former Member]

Can anyone share knowledge about this please?

[Former Member]

the check table is same for all fields that is ZTXT005S and there is 2 different foreign key table that is ZTXLFA1 and ZTXLFA1CC 

My friend...I think you are confusing yourself on what is a check table and what is a foreign key table.

See the check field REGIO belongs to ZTXLFA1 and has check table as ZTXT005S on field REGIO.

Now LAND1 in ZTXT005S is checked against ZTXLFA1CC-LAND1 (which could be implied as another foreign key relationship with ZTXTLFA1CC as the check table for ZTXT005S)

The entire proposal ZTXTLFA1-REGIO is generated like this

ZTXTLFA1 ZTXT005S ZTXTLFA1CC

MANDT MANDT MANDT

REGIO -

---

> REGIO Here the check table is ZTXT005S only for this proposal

LAND1 -

---

> LAND1 Here the check table is ZTXTLFA1CC only for this proposal

[Former Member]

I have done what you told. Tables and fields are as follows.

table

1. ZTXLFA1 consists of 3 fields EMPNO(primary key) and REGIO

created foreign key ZTXLFA1 - REGIO against ZTXT005S - REGIO

ZTXT005S - MANDT = ZTXLFA1 - MANDT

ZTXT005S - LAND1 = ZTXLFA1CC - LAND1

ZTXT005S - REGIO = ZTXLFA1 - REGIO

2. ZTXT005S(ZTXT005S - REGIO) consists of 2 fields REGIO AND LAND1

created foreign key LAND1 against ZTXTLFA1CC - LAND1

Values

NY, US

TK, JP

3. ZTXLFA1CC(CHECK TABLE FOR ZTXTLFA1CC - LAND1) consists of a single field LAND1

Values

US

JP

WHEN i ENTER "NY" INTO ZTXLFA1 IT SAYS "ENTRY NY DOES NOT EXIST IN ZTXT005S"

You have not cleared one important thing that I asked couple of times

In your example both LAND1 and REGIO belong to the same table then how it would be an adapted foreign key? this is my question.

I'm not sure I can ask this. Could you please let me know your contact number or email id so that we could talk over phone or chat?

From "SAP - Sams Teach Yourself ABAP 4 in 21 Days"

*Foreign key fields of a compound foreign key do not all have to reside in the same table. If they do not, this is known as an adapted foreign key. For example, assume that the country code for the vendor is not stored at all in ztxlfa1, but instead in ztxlfa1cc. When you create the foreign key on the region field, you would change the check table field for land1 to ztxlfa1cc-land1 (see Figure 4.12). The validation on the regio field would check to see whether the combination of mandt and regio existed in ztxt005s and whether land1 existed in any row of ztxt005cc.

WHICH IS THIS TABLE "ZTXT005CC"?

Edited by: prince_1982 on Feb 10, 2012 6:10 AM

Edited by: prince_1982 on Feb 10, 2012 6:13 AM

Edited by: prince_1982 on Feb 10, 2012 6:14 AM

Edited by: prince_1982 on Feb 10, 2012 6:16 AM

[Former Member]

WHEN i ENTER "NY" INTO ZTXLFA1 IT SAYS "ENTRY NY DOES NOT EXIST IN ZTXT005S"

I'm getiing the same error too....strange

In your example both LAND1 and REGIO belong to the same table then how it would be an adapted foreign key? this is my question.

Coz they are being checked against different tables...

WHICH IS THIS TABLE "ZTXT005CC"? 

I think its a typo. He means ZTXTLFA1CC

Lemme check once i'm home and try and analyse it.

[Former Member]

Hello SAP Experts!

Please... please...Don't ignore like this... help me!

Edited by: prince_1982 on Feb 11, 2012 5:38 PM

[Former Member]

Sorry for the late reply, it being a weekend and all.

Unfortunately, I'm out of answers on this behavior. This is how adapted keys were meant to be.

[Former Member]

I'm a novice. It seems you are here since 2010 so you might be knowing senior members. can you please ask whether they also get the same problem? don't you want to figure it out?

[Former Member]

hey please see the following link.

Clemens Li said "Hint: Apply the check table for country to the underlying domain"

http://forums.sdn.sap.com/thread.jspa?threadID=1881744

I did not understand.

Edited by: prince_1982 on Feb 12, 2012 8:27 AM

[Former Member]

any help???