oa_fkeys

This is a system table that contains information about foreign keys referenced by each table and the primary keys in each table.

A row in the oa_fkeys table is considered a primary key when the following conditions are met: the fktable_name value is NULL and the fkcolumn_name value is NOT NULL.

Example Queries

• To find all tables that reference a specific table, use the following query:

                select pktable_name, pkcolumn_name, fktable_name, fkcolumn_name, fk_name from oa_fkeys where pktable_name = 'TABLE_NAME'; 
  
              

  For example, if you try to find all tables that reference the accounts table, the output result may include the following rows:

  pktable_name	pkcolumn_name	fktable_name	fkcolumn_name	fk_name
  ACCOUNTS	ACCOUNT_ID	EXPENSE_ACCOUNTS	EXPENSE_ACCOUNT_ID	EXPENSE_ACCOUNTS_ACCOUNTS_FK
  ACCOUNTS	ACCOUNT_ID	TRANSACTION_LINES	ACCOUNT_ID	TRANSACTION_LINES_ACCOUNTS_FK

• To find all tables that are referenced by a specific table, use the following query:

                select pktable_name, pkcolumn_name, fktable_name, fkcolumn_name, fk_name from oa_fkeys where fktable_name = 'TABLE_NAME'; 
  
              

  For example, if you try to find all tables that are referenced by the accounts table, the output result may include the following rows:

  pktable_name	pkcolumn_name	fktable_name	fkcolumn_name	fk_name
  ACCOUNTS	ACCOUNT_ID	ACCOUNTS	DEFERRAL_ACCOUNT_ID	ACCOUNTS_ACCOUNTS_FK
  ACCOUNTS	ACCOUNT_ID	ACCOUNTS	PARENT_ID	ACCOUNTS_ACCOUNTS_FK_2
  ACCOUNTS	ACCOUNT_ID	EXPENSE_ACCOUNTS	EXPENSE_ACCOUNT_ID	EXPENSE_ACCOUNTS_ACCOUNTS_FK
  ACCOUNTS	ACCOUNT_ID	TRANSACTION_LINES	ACCOUNT_ID	TRANSACTION_LINES_ACCOUNTS_FK

  Please note that the table may reference itself.

• To find all tables that contain a specific column as the primary key, use the following query:

                select pktable_name, pkcolumn_name, key_seq from oa_fkeys where pkcolumn_name = 'COLUMN_NAME'; 
  
              

  For example, if you try to search for the tables that contain the location_id column as the primary key, the output may include the following rows:

  pktable_name	pkcolumn_name	key_seq
  LOCATIONS	LOCATION_ID	1
  SUBSIDIARY_LOCATION_MAP	LOCATION_ID	2

  In this example, the location_id column is the primary key in both the locations and the subsidiary_location_map tables. However, the subsidiary_location_map table has a composite primary key, consisting of two primary key columns: subsidiary_id and location_id. The subsidiary_id column is the first in the primary key sequence, and the location_id column is the second.

• To find all tables that contain a specific column as a foreign key and see which tables include that column as the primary key, use the following query:

                select fktable_name, fkcolumn_name, pktable_name, pkcolumn_name, key_seq from oa_fkeys where fkcolumn_name = 'COLUMN_NAME'; 
  
              

  For example, if you try to find all tables that contain the location_id column as a foreign key, the output may include the following rows:

  fktable_name	fkcolumn_name	pktable_name	pkcolumn_name	key_seq
  ACCOUNTS	LOCATION_ID	LOCATIONS	LOCATION_ID	1
  SUBSIDIARY_LOCATION_MAP	LOCATION_ID	LOCATIONS	LOCATION_ID	1

  In this example, location_id is a foreign key column in the accounts and subsidiary_location_map tables, whereas in the locations table it is the primary key. This means that both the accounts and the subsidiary_location_map tables are related to the locations table through the location_id column.

General Notices