Search Results fnd_foreign_keys




Overview
Explore in AI Chat

Overview

The FND_FOREIGN_KEYS table is a core metadata repository within the Application Object Library (FND) of Oracle E-Business Suite (EBS) 12.1.1 and 12.2.2. It serves as the central registry for all foreign key relationships that have been formally registered with the Oracle Application Object Library. This registration is a critical component of the EBS architecture, enabling the system to understand and enforce referential integrity constraints between application tables at a metadata level. The table's existence supports various framework functionalities, including application installation, upgrade scripts, and data integrity validation tools, by providing a declarative source of truth for table relationships beyond the physical database constraints.

Key Information Stored

The table stores metadata that uniquely identifies and describes each registered foreign key. Its structure is defined by composite primary and unique keys, ensuring data integrity within the metadata itself. The most critical columns include APPLICATION_ID, which links to the owning application; TABLE_ID, which identifies the child table containing the foreign key; and FOREIGN_KEY_ID or FOREIGN_KEY_NAME, which uniquely identifies the key constraint within the context of its table. Furthermore, it references the associated primary key through the columns PRIMARY_KEY_APPLICATION_ID, PRIMARY_KEY_TABLE_ID, and PRIMARY_KEY_ID, which point to the corresponding record in the FND_PRIMARY_KEYS table. This design creates a complete metadata map of the relationship from the foreign key to its referenced primary key.

Common Use Cases and Queries

Primary use cases involve impact analysis, dependency tracking, and system documentation. Developers and DBAs query this table to understand data model relationships before performing major data migrations or schema changes. A typical query retrieves all foreign keys for a specific table to assess dependencies.

  • Finding Foreign Keys for a Table: SELECT fk.foreign_key_name, pk.table_name referenced_table FROM fnd_foreign_keys fk JOIN fnd_tables ft ON fk.table_id = ft.table_id AND fk.application_id = ft.application_id JOIN fnd_tables pk ON fk.primary_key_table_id = pk.table_id AND fk.primary_key_application_id = pk.application_id WHERE ft.table_name = 'PO_HEADERS_ALL';
  • Identifying Unregistered Foreign Keys: Comparing constraints from USER_CONSTRAINTS with entries in FND_FOREIGN_KEYS to find physical constraints not registered in AOL, which is crucial for compliance in custom developments.

Related Objects

FND_FOREIGN_KEYS is part of a tightly integrated metadata suite within the APPLSYS schema. Its most direct relationships are defined by its foreign key constraints, as documented in the ETRM metadata.

  • FND_TABLES: References this table via APPLICATION_ID and TABLE_ID to identify the child table owning the foreign key.
  • FND_PRIMARY_KEYS: References this table via PRIMARY_KEY_APPLICATION_ID, PRIMARY_KEY_TABLE_ID, and PRIMARY_KEY_ID to map to the parent table's primary key constraint.
  • FND_FOREIGN_KEY_COLUMNS: This is a critical child table that stores the specific column mappings for each foreign key defined in FND_FOREIGN_KEYS, detailing which local column references which primary key column.