Search Results fnd_foreign_key_columns_u1

Overview

APPLSYS.FND_FOREIGN_KEY_COLUMNS is an Oracle E-Business Suite repository table that stores metadata describing individual columns comprising foreign key definitions registered in the Applications dictionary. Each row captures one column of one foreign key, together with its ordinal position within that key and any cascade behavior associated with it. In EBS 12.1.1 and 12.2.2, this table is a seed data object housed in the APPS_TS_SEED tablespace, and Oracle Applications relies on its contents during database installation, patching, and upgrade operations to construct, validate, and maintain referential integrity across the FND schema metadata layer.

From a Data Vault modeling perspective, the mined foreign key structure suggests classifying this object as a link: it resolves the many-to-many relationship between foreign key definitions and the columns that participate in them, rather than acting as a standalone hub or a descriptive satellite.

Key Information Stored

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

• APPLICATION_ID — identifies the application owning the foreign key metadata.
• TABLE_ID — identifies the table on which the foreign key is defined.
• FOREIGN_KEY_ID — identifies the parent foreign key definition.
• FOREIGN_KEY_SEQUENCE — the ordinal position of the column within the foreign key.
• COLUMN_ID — identifies the specific column participating in the key.
• CASCADE_VALUE — a VARCHAR2(80) value describing the cascade behavior for the key.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN — standard Who columns providing audit lineage.
• ZD_EDITION_NAME — the editioning column present in the 12.2.2 physical schema, supporting Edition-Based Redefinition.

The surrogate primary key is FND_FOREIGN_KEY_COLUMNS_PK on (APPLICATION_ID, TABLE_ID, FOREIGN_KEY_ID, FOREIGN_KEY_SEQUENCE). Two business-key candidates are enforced by unique indexes: FND_FOREIGN_KEY_COLUMNS_U1 on (APPLICATION_ID, TABLE_ID, FOREIGN_KEY_ID, FOREIGN_KEY_SEQUENCE, ZD_EDITION_NAME), and FND_FOREIGN_KEY_COLUMNS_U2 on (APPLICATION_ID, TABLE_ID, COLUMN_ID, FOREIGN_KEY_ID, ZD_EDITION_NAME). Because the user search term was fnd_foreign_key_columns_u2, that index is the relevant access path: it guarantees that a given column participates at most once within a given foreign key, while enabling efficient lookups by column.

Common Use Cases and Queries

Typical scenarios include reconstructing a foreign key definition column-by-column, auditing referential integrity before an upgrade, and documenting the FND data model. A query resolving the columns of a specific foreign key follows the primary key access path:

• SELECT FOREIGN_KEY_SEQUENCE, COLUMN_ID, CASCADE_VALUE FROM APPLSYS.FND_FOREIGN_KEY_COLUMNS WHERE APPLICATION_ID = :app AND TABLE_ID = :tbl AND FOREIGN_KEY_ID = :fk ORDER BY FOREIGN_KEY_SEQUENCE;
• Lookups driven by FND_FOREIGN_KEY_COLUMNS_U2 reverse the relationship, finding every foreign key that uses a given column: WHERE APPLICATION_ID = :app AND TABLE_ID = :tbl AND COLUMN_ID = :col.
• Reporting on cascade behavior can group by CASCADE_VALUE to inventory keys configured for cascading operations.

Related Objects

This table sits within a tightly coupled metadata family:

• APPLSYS.FND_FOREIGN_KEYS — the parent key definition, joined on APPLICATION_ID (and FOREIGN_KEY_ID).
• APPLSYS.FND_COLUMNS — the column dictionary, joined on APPLICATION_ID and COLUMN_ID, supplying column names and datatypes.
• FND_FOREIGN_KEYS and FND_COLUMNS are the two documented FK targets of APPLICATION_ID.
• Dependent code objects include APPS views, ETRM_FNDNAV, and ETRM_RPT, plus the FND_FOREIGN_ package family, all of which reference this table for dictionary navigation and reporting.