Search Results fnd_foreign_key_columns




Overview
Explore in AI Chat

Overview

FND_FOREIGN_KEY_COLUMNS is a metadata repository table owned by the APPLSYS schema and maintained by the FND - Application Object Library product in Oracle E-Business Suite 12.1.1 and 12.2.2. It stores the individual column-level definitions that make up every foreign key relationship registered with Oracle Application Object Library. Where FND_FOREIGN_KEYS records the foreign key as a whole, FND_FOREIGN_KEY_COLUMNS decomposes each key into its participating columns, preserving the ordinal position of each column within the key through FOREIGN_KEY_SEQUENCE. This makes it part of the EBS application dictionary infrastructure that drives automatic foreign key enforcement, entity relationship modeling, and dictionary-driven runtime behavior.

From a heuristic Data Vault modeling perspective, this object is best classified as a link, since it resolves and connects two hubs — the foreign key definition and the underlying column definition — creating an association between them. The classification is a modeling suggestion only and does not reflect a physical constraint enforced by the product.

Key Information Stored

The table contains twelve documented columns. The most significant are:

The surrogate primary key is FND_FOREIGN_KEY_COLUMNS_PK, defined over APPLICATION_ID, TABLE_ID, FOREIGN_KEY_ID, and FOREIGN_KEY_SEQUENCE. Two business-key candidates exist as unique indexes: FND_FOREIGN_KEY_COLUMNS_U1 extends the primary key columns with ZD_EDITION_NAME, and FND_FOREIGN_KEY_COLUMNS_U2 provides an alternate key over APPLICATION_ID, TABLE_ID, COLUMN_ID, FOREIGN_KEY_ID, and ZD_EDITION_NAME. These unique indexes enforce that a given foreign key column and position are registered only once per edition.

Common Use Cases and Queries

Typical scenarios include tracing foreign key definitions for a table, validating which columns participate in a relationship, and generating entity relationship documentation from the application dictionary. A representative query joining the parent key definition is:

  • SELECT fkc.table_id, fkc.foreign_key_id, fkc.foreign_key_sequence, fkc.column_id FROM applsys.fnd_foreign_key_columns fkc WHERE fkc.table_id = :table_id ORDER BY fkc.foreign_key_id, fkc.foreign_key_sequence;
  • Join FND_FOREIGN_KEYS fk ON fk.application_id = fkc.application_id AND fk.table_id = fkc.table_id AND fk.foreign_key_id = fkc.foreign_key_id to retrieve key-level attributes.
  • Join FND_COLUMNS fc ON fc.application_id = fkc.application_id AND fc.table_id = fkc.table_id AND fc.column_id = fkc.column_id to obtain column names.

Reporting use cases include application dictionary extract reports, impact analysis before column changes, and dependency documentation for upgrade or customization projects.

Related Objects

The principal related objects, based on the documented foreign key relationships, are:

  • FND_FOREIGN_KEYS — parent table, joined on APPLICATION_ID, TABLE_ID, and FOREIGN_KEY_ID.
  • FND_COLUMNS — referenced column definitions, joined on APPLICATION_ID, TABLE_ID, and COLUMN_ID.
  • FND_TABLES — the table registration that qualifies TABLE_ID.
  • FND_APPLICATION — provides application context for APPLICATION_ID.
  • AD_DD / AD_DD_API — the dictionary utility layer that populates and reads this metadata during installation and patching.

These objects together form the relational backbone of the EBS application dictionary and should be treated as internal metadata rather than end-user data.