DB Tables & Foreign Key


 Business Scenario: Suppose we are Maintaining  Employee Details and Every Employee has to be assigned to a Corresponding Department. So the prerequisite is, all the departments should be available first and then when a Employee master data is created, we can assign only the available Department to the Employee.

Table 1 : Department Table (Independent Table/Master Table/Check Table)

Table 2 : Employee Table    (Dependent Table/  Child Table/    Foreign Key Table)

Lets Create the tables with all field details, Create the Table Maintenance Generator, Maintaining the Foreign Key Relationships, Maintaining the Entries and Validating the Entries.

CREATING THE DEPARTMENT TABLE: ZEMP_DEPT

Step 1. Go to TCODE- SE11, Select the Database Table Radio Button and Provide the Table  Name: ZEMP_DEPT and Click on the ‘CREATE’ Button.

Step 2. Provide the Short Description, Click the ‘Delivery and Maintenance’ tab. Provide the Delivery Class as ‘C’ and in the Data Browser/Table View Maint. Select the   Display / Maint allowed. At last click on the Technical Settings Button on the Application tool bar.

Step 3.  Provide the Data Class ‘APPL0’ and the Size Category as: ‘0’. Then Click on the SAVE  button and then click on the BACK button.

Step 4.  First create all the required Domains and Data Elements as per the required data types     and length so that we can assign the data element against the fields. Provide all the fields and corresponding data elements.Mark MANDT and DEPT_ID as  primary key for this Department table. Finally Click on the Activate Button to activate  the Table.

Step 5. In order to maintain multiple entries in the table at a time, we need to crate a Table Maintenance Generator (TMG). Click on the Utilities, then click on the Table Maintenance Generator Menu Bar.

Step 6. First provide the Authorization Object: ‘&NC&’ , then provide the Function Group Name as ‘ZEMP_DEPT’ (The TMG Function group name normally same as the table name and it is going to hold all the screen logics that is automatically generated when TMG is created). Select Maintenance type as One Step and then click on the button ‘Find Scr. number(s)’.

Step 7. Select the First radio button ‘Propose Screen number(s)’ and click on the Tick Button.

 

 Step 8.  Automatically the Overview Screen is filled with some value and then click on the 

‘CREATE’ button to create the TMG as shown on the below diagram.



Step 9.  At last click on the ‘SAVE’ button. Save as local Object.

Step 10. If the TMG for the Table is created successfully then a status message is shown as below: ‘Request Completed without Errors’ and  then click on the back button.

Step 11. Go to UTILITIES, then TABLE CONTENTS and then click on CREATE ENTRIES.

Step 12. Click on NEW ENTRIES button.

Step 13. Maintain the DEPT ID and DEPT NAME and  click on the ‘SAVE’ button and at last click on the BACK button.

Step 14. Now click on ‘UTILITIES’, then ‘TABLE CONTENTS’ and then click on ‘DISPLAY’.

Step 15. Click on the ‘EXECUTE’ button or else click the F8 button to see all the entries of the table.

Step 16.  Now all the required Departments are available with their DEPT ID AND DEPT NAME.



CREATING THE EMPLOYEE TABLE: 

Step 1. Go to TCODE- SE11, Select the Database table Radio button and provide the table name ‘ZEMPLOYEE’ and then click on the CREATE button.

Step 2. Provide the short description, Provide Delivery Class as ‘A’ and choose 

‘Display/Maintenance Allowed’. At last click on the ‘TECHNICAL SETTINGS’ button.

Step 3. Provide the delivery class as ‘APPL0’ and the Size Category as ‘0’. click on SAVE and then click on the BACK button.

Step 4. Create all the domains and data elements as per the requirement. Provide the field names with appropriate data elements. Select the MANDT and the EMP_ID field as the Primary key fields. Maintain the data element ‘ZDEPT_ID’ against the DEPT field, the same data element as used the the above DEPT table.

Step 5. Now we have to maintain the Foreign key for the DEPT field. So select the particular row and click on the FOREIGN KEY Button.

Step 6.  Provide the check table name ZEMP_DEPT and click on the GENERATE PROPOSAL button.

Step 7. After clicking the Generate Proposal button , system will automatically populates the matching domains between the check table and the foreign key table. Just click on the COPY button.

Step 8. Create the TMG for the table and then create the table entries as per the path shown.

Step 9. click on CREATE ENTRIES button to create Employee Details.

Step 10. Provide the Employee details and provide the DEPT ID as any id that does not exist in the DEPT table and hit the ENTER button and the system will through the below error message as highlighted below.

Step 11. So click on the F4 Button and it will show all the possible values from the DEPT table for the field DEPT_ID in the EMPLOYEE TABLE . Choose any one and hit the ENTER button.

Step 12. Save the Entry by clicking on the SAVE button. Similarly Create other employee details  and SAVE the entries and click on the BACK button.

Step 13. Click on the highlighted button to see the table entries.

Step 14. Click on the EXECUTE button or hit F8 key .

Step 15. Now the employee is only assigned to the departments that are only available in the DEPT table.


 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s