Search Results fnd_index_columns_uk1

Overview

The FND_INDEX_COLUMNS table is a core metadata repository within the Oracle E-Business Suite Application Object Library (FND). It serves as the central registry for the column-level composition of indexes that are registered with the Oracle Application Object Library. This table does not store the physical database index definitions themselves; rather, it maintains the application-tier metadata that defines which columns belong to a specific application index and their order within that index. This registration is critical for EBS's self-describing architecture, enabling various application utilities and maintenance operations to understand and manage index structures programmatically.

Key Information Stored

The table's structure is designed to uniquely identify a column's position within a registered index. Its primary key (FND_INDEX_COLUMNS_PK) consists of APPLICATION_ID, TABLE_ID, INDEX_ID, and COLUMN_SEQUENCE, which together define the exact order of columns for an index. A unique key (FND_INDEX_COLUMNS_UK1) also exists on APPLICATION_ID, TABLE_ID, INDEX_ID, and COLUMN_ID to prevent the same column from being added multiple times to the same index definition. The most significant columns are APPLICATION_ID (linking to the owning application), TABLE_ID (linking to the FND_TABLES metadata), INDEX_ID (linking to the parent index definition in FND_INDEXES), COLUMN_ID (linking to the specific column in FND_COLUMNS), and COLUMN_SEQUENCE (defining the ordinal position of the column within the index).

Common Use Cases and Queries

This table is primarily accessed for metadata reporting, impact analysis, and system validation. A common use case is generating a report of all registered indexes and their constituent columns for a specific application table to verify metadata alignment with the physical database. Developers and DBAs query this table to understand dependencies before modifying a registered column or index. A typical query joins FND_INDEX_COLUMNS with FND_INDEXES, FND_TABLES, and FND_COLUMNS to produce a human-readable list.

• Sample Query: SELECT fi.INDEX_NAME, fc.COLUMN_NAME, fic.COLUMN_SEQUENCE FROM FND_INDEX_COLUMNS fic, FND_INDEXES fi, FND_COLUMNS fc WHERE fic.INDEX_ID = fi.INDEX_ID AND fic.TABLE_ID = fi.TABLE_ID AND fic.APPLICATION_ID = fi.APPLICATION_ID AND fic.COLUMN_ID = fc.COLUMN_ID AND fic.TABLE_ID = fc.TABLE_ID AND fic.APPLICATION_ID = fc.APPLICATION_ID AND fi.TABLE_ID = (SELECT TABLE_ID FROM FND_TABLES WHERE TABLE_NAME = '&TABLE_NAME') ORDER BY fi.INDEX_NAME, fic.COLUMN_SEQUENCE;

Related Objects

FND_INDEX_COLUMNS is a child table in two key foreign key relationships within the Application Object Library's metadata model, as documented in the ETRM.

• FND_INDEXES: The table has a foreign key relationship where the columns (APPLICATION_ID, TABLE_ID, INDEX_ID) in FND_INDEX_COLUMNS reference the primary key of the FND_INDEXES table. This defines the parent index for the column entries.
• FND_COLUMNS: A second foreign key exists where the columns (APPLICATION_ID, TABLE_ID, COLUMN_ID) in FND_INDEX_COLUMNS reference the primary key of the FND_COLUMNS table. This links the index column entry to the specific application column metadata.

These relationships ensure referential integrity, guaranteeing that every registered index column corresponds to a valid registered index and a valid registered application column.