{"id":22577,"date":"2026-08-04T10:43:26","date_gmt":"2026-08-04T10:43:26","guid":{"rendered":"https:\/\/tenthplanet.in\/idempiere\/?p=22577"},"modified":"2026-08-13T04:45:37","modified_gmt":"2026-08-13T04:45:37","slug":"how-to-configure-create-foreign-key-in-idempiere","status":"publish","type":"post","link":"https:\/\/tenthplanet.in\/idempiere\/how-to-configure-create-foreign-key-in-idempiere\/","title":{"rendered":"How to Configure Create Foreign Key in iDempiere"},"content":{"rendered":"\n

The Create Foreign Key window is a System Administration process in iDempiere that generates and synchronizes database-level foreign key constraints based on the Reference Table settings defined on AD_Column.<\/p>\n\n\n\n

Running this process protects referential integrity across sales, inventory, and customer data, preventing orphaned records as store-specific customizations grow.<\/p>\n\n\n\n

Process Flow<\/strong><\/h2>\n\n\n\n
\"\"<\/figure>\n\n\n\n

Business Rules<\/strong><\/h2>\n\n\n\n
    \n
  • Only columns with a Reference Table set on AD_Column are eligible for foreign key creation.<\/li>\n\n\n\n
  • Key columns and UUID columns are excluded from foreign key generation automatically.<\/li>\n\n\n\n
  • AD_Client_ID, AD_Org_ID, CreatedBy and UpdatedBy columns are skipped by default.<\/li>\n\n\n\n
  • Existing database constraints are compared against AD_Column settings before any change.<\/li>\n\n\n\n
  • Mismatched constraints are dropped and recreated to match the FK Constraint Type on the column.<\/li>\n\n\n\n
  • Running at table or database level processes every eligible column found in that scope.<\/li>\n\n\n\n
  • The process can be scheduled as a background job for large retail table syncs.<\/li>\n<\/ul>\n\n\n\n

    Prerequisites<\/strong><\/h2>\n\n\n\n
      \n
    • Custom table registered in Application Dictionary (Table and Column window).<\/li>\n\n\n\n
    • Column created with the correct Reference Table pointing to the parent table.<\/li>\n\n\n\n
    • FK Constraint Type set on the column, if non-default behavior is required.<\/li>\n\n\n\n
    • Database user has ALTER TABLE privileges on the target schema.<\/li>\n\n\n\n
    • Table synchronized with the physical database (Sync This Column \/ Table completed).<\/li>\n<\/ul>\n\n\n\n
      Note: <\/strong>Leaving Table blank scans every registered table in the database and may take significant time on production retail environments.<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

      Navigation<\/strong><\/h2>\n\n\n\n

      Menu \u2192 System Admin \u2192 General Rules \u2192 Database \u2192 Create Foreign Key<\/p>\n\n\n\n

      Configuration Steps<\/strong><\/h2>\n\n\n\n

      Step 1: Open the Create Foreign Key Process<\/strong><\/h3>\n\n\n\n

      Use the search bar and type “creat” to filter, then select Create Foreign Key from the process list to open the parameter window.<\/p>\n\n\n\n

      \"\"<\/figure>\n\n\n\n

      Step 2: Select the Target Table<\/strong><\/h3>\n\n\n\n

      Choose the table from the Table dropdown, for example C_InvoiceLine. Leave Column blank to process all eligible columns on the table.<\/p>\n\n\n\n

      Step 3: Select a Specific Column (Optional)<\/strong><\/h3>\n\n\n\n

      Select the column from the Column dropdown, for example QtyInvoiced. Leave it blank to process the entire table instead.<\/p>\n\n\n\n

      Field Reference Table<\/strong><\/h3>\n\n\n\n
      Field<\/strong><\/td>Sample Value<\/strong><\/td>Description<\/strong><\/td><\/tr><\/thead>
      Table<\/td>C_InvoiceLine_Invoice Line<\/td>Table whose columns are scanned for foreign key sync<\/td><\/tr>
      Column<\/td>QtyInvoiced_Quantity Invoiced<\/td>Specific column to process; optional<\/td><\/tr>
      Run as Job<\/td>Unchecked<\/td>Runs the process in the background scheduler queue<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

      Step 4: Choose Run as Job for Large Syncs<\/strong><\/h3>\n\n\n\n

      Check Run as Job before clicking OK when syncing high-volume transaction tables during off-peak hours.<\/p>\n\n\n\n

      Step 5: Execute and Review the Result Log<\/strong><\/h3>\n\n\n\n

      Click OK to run. Review the result message showing table count, foreign keys created, and error count in the process log.<\/p>\n\n\n\n

      Common Issues and Resolutions<\/strong><\/h2>\n\n\n\n
      Issue<\/strong><\/td>Cause<\/strong><\/td>Resolution<\/strong><\/td><\/tr><\/thead>
      No foreign key created<\/td>Column has no Reference Table set<\/td>Set Reference Table on the column in Table and Column window<\/td><\/tr>
      Constraint already exists error<\/td>A manually created DB constraint uses a different name<\/td>Let the process detect and reuse the existing constraint name<\/td><\/tr>
      Column skipped silently<\/td>Column is AD_Client_ID, AD_Org_ID, CreatedBy or UpdatedBy<\/td>Set FK Constraint Type explicitly if a constraint is really needed<\/td><\/tr>
      Process runs slowly on large tables<\/td>Full database or table-level sync scans many columns<\/td>Enable Run as Job and schedule during low-traffic hours<\/td><\/tr>
      Constraint dropped unexpectedly<\/td>FK Constraint Type set to Do Not Create – Ignore<\/td>Change FK Constraint Type on the column before rerunning<\/td><\/tr>
      Error count greater than zero<\/td>Data violates referential integrity (orphan records)<\/td>Clean up orphan child records before recreating the constraint<\/td><\/tr>
      Key or UUID column not processed<\/td>Key and UUID columns are excluded by design<\/td>Expected behavior; no action needed<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

      Next Steps<\/strong><\/h2>\n\n\n\n
        \n
      • Table and Column window \u2013 define Reference Table and FK Constraint Type.<\/li>\n\n\n\n
      • Sync This Column \/ Sync Database process \u2013 align dictionary with DB schema.<\/li>\n\n\n\n
      • Reference and Reference List windows \u2013 set up lookup values for new columns.<\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"

        The Create Foreign Key window is a System Administration process in iDempiere that generates and synchronizes database-level foreign key constraints […]<\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[10,95],"tags":[],"class_list":["post-22577","post","type-post","status-publish","format-standard","hentry","category-idempiere-product","category-idempiere-application-dictionary"],"_links":{"self":[{"href":"https:\/\/tenthplanet.in\/idempiere\/wp-json\/wp\/v2\/posts\/22577","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/tenthplanet.in\/idempiere\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/tenthplanet.in\/idempiere\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/tenthplanet.in\/idempiere\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/tenthplanet.in\/idempiere\/wp-json\/wp\/v2\/comments?post=22577"}],"version-history":[{"count":1,"href":"https:\/\/tenthplanet.in\/idempiere\/wp-json\/wp\/v2\/posts\/22577\/revisions"}],"predecessor-version":[{"id":22580,"href":"https:\/\/tenthplanet.in\/idempiere\/wp-json\/wp\/v2\/posts\/22577\/revisions\/22580"}],"wp:attachment":[{"href":"https:\/\/tenthplanet.in\/idempiere\/wp-json\/wp\/v2\/media?parent=22577"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/tenthplanet.in\/idempiere\/wp-json\/wp\/v2\/categories?post=22577"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/tenthplanet.in\/idempiere\/wp-json\/wp\/v2\/tags?post=22577"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}