Problem with Foreign Key relationships in SAP R/3 4.7

[Former Member]

Hi Experts,

I am trying to create a foreign key relationship between 2 transparent tables in SAP R/3 4.7

Table 1:ZAAVNDR (MANDT (pk), VENDORNO (pk), NAME, REGION, COUNTRY (fk)) Foreign Key Table

Table 2: ZAAVNDRREF(MANDT(pk), COUNTRY (pk)) ---Check table

I have added few valid countries in check table but when I am adding some records in foreign key table with invalid countries these records are not being restricted and are still successfully going into the table.

Could any one please help in this.

Thanks in anticipation.

-Amit

[Former Member]

In both the table COUNTRY field should be Primary Key.

In your case , in table ZAAVNDR , it is not.

Make COUNTRY as primary key in ZAAVNDR table.

It will work as your requirment.

Regards,

Anirban

[Former Member]

yes same problem i have faced.. both key are primary key only,for my requirement. eventhough its wont work for me..

Edited by: partha sarathi on Aug 13, 2009 10:05 AM

[Former Member]

Hi Anirban,

Thanks for your reply. I have made Country primary key in both the tables but still the records are not being restricted. Could you please check.

Thanks

-Amit

[Former Member]

Check

1. The domain of the COUNTRY field in both the table are same.

2. The Foreignkey relationship is created properly .

3. all the table and components are active.

Anirban

[Former Member]

How are you adding entries to the table? If you are doing it through a program via an INSERT statement, then the check is not performed. You will have to do that in your program.

[Former Member]

Hi,

I am adding records by using the Menu (Utilities->Table Contents->Create Entries). Even after making Country PK in both the tables still the records are not being restricted.

Could you please help.

Thanks

-Amit

[mohammed_moqeeth]

Hi,

Please go through following points, I hope it will helpful for you.

1. Maintain primary key in field 'COUNTRY' in both the tables (foreign key table & check table). Note: both fileds should have same data element.

2. Maintain foreign key relation for field 'COUNTRY' in foreign key table.

3. Since table is changed so you need to "Activate and Adjust Table" using transaction SE14

Regards,

Moqeeth

[ThomasZloch]

From the previous replies I think that maintaining the foreign key relationship for country code via SE11 in the vendor table is essential, however the primary key should remain unchanged. I mean, how can you recommend changing the primary key to country code in a table with data related to vendors?

Thomas

[Former Member]

While defining the foreign key relationship please check to see if the 'Check Required' flag in the 'Screen Check' section has been selected.

[Former Member]

Hi Guys,

I have done everything what have been said in the replies like:

1. Putting Country code as primary key in zaavndr table (which ideally should not as this table contains vendor specific data) and also check required (is checked) while creating the foreign key relationship.

Could anyone please help and create these 2 small sample tables and try it out if it is working.

Thanks in anticipation.

-Amit

[Sandra_Rossi]

I did it exactly as you said, and there is an error when I try to add a record via SE16 in ZAAVNDR with an invalid country (which does not exist in ZAAVNDRREF). All my fields refer to data element CHAR10, except MANDT of course which refers to MANDT.

The foreign key is like this:

Check table  ChkTabFld  For.key table For.key field
ZAAVNDRREF   MANDT      ZAAVNDR       MANDT
ZAAVNDRREF   COUNTRY    ZAAVNDR       COUNTRY

The advices above are good, I don't understand why you have this issue.

Could you please recreate new tables from scratch (change table names), exactly as I did (it took me 2 minutes), and tell us if it works for you or not.

[Former Member]

Hi Sandra,

Many thanks for your response and providing time of yours.

Now, I have done exactly the same thing, but still it is the same.

I have created two new tables as below:

ZAAVREF (Check table)

MANDT (PK)

COUNTRY (PK) Domain:ZAACOUNT (CHAR 10)

ZAAV1 (Foreign key table)

MANDT (PK)

COUNTRY (PK) Domain:ZAACOUNT (CHAR 10)

Then I have created FK on country of foreign key table ZAAV1 and then SE16 (for table ZAAVREF)->Create Entries-> Entered values for Country only->Save....Records entered with valid Country values.

After that SE16 (for table ZAAV1)->Create Entries-->Entered an Invalid country->Save->Still the record entered to the Database successfully....

Could you please let me know where I am going wrong.

I am using SAP R/3 4.7 and creating tables using Tools->ABAP Workbench->Development->ABAP dictionary

[Sandra_Rossi]

In fact, I had tested with your first version of ZAAVNDR (columns VENDORNO, etc.) which made sense.

Anyway, I did the test as you did last, and it works for me (SAP 7.0).

Could you please use data element CHAR10 instead of data element ZAACOUNT, and test again. It's the only difference between your test and mine.

Edited by: Sandra Rossi on Aug 15, 2009 11:45 PM ---> "SAP 4.7" changed to "SAP 7.0"

[Former Member]

Hi Sandra,

Are you testing in SAP R/3 4.7 or some other version. I did the same and still it is the same behaviour. Can I call you to resolve the problem. I am currently in Canada and if you can let me know your number, probably i can call you.

Any ways thanks for your help.

-Amit

[Sandra_Rossi]

In fact, I tested it on 7.0. I looked at SAP notes, there is nothing related to your issue.

So please check again everything.

Another test you can do if you are definitely stuck, enter a value in SCARR database table, then in SPFLI enter an invalid CARRID value, you should get an error message. Compare what you did, with these 2 tables (the foreign key is at SPFLI-CARRID level, which points to SCARR-CARRID).

[Sandra_Rossi]

You marked the thread as answered, what was the issue?

[Former Member]

Did it by mistake. Still the problem is not resolved.