oa_fkeys

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

Note:

Due to the complexity of the NetSuite2.com schema, the oa_fkeys table may provide inaccurate information about foreign keys for the NetSuite2.com data source. Some foreign keys may be missing or incorrectly identified as such. Consider the information about foreign keys for NetSuite2.com as a reference only.

A row in the oa_fkeys table is considered a primary key if its pktable_name and pkcolumn_name values are not NULL.

Table name: oa_fkeys
Column Name	Description
pktable_qualifier	Primary key table qualifier
pktable_owner	Primary key table owner
pktable_name	Primary key table name
pkcolumn_name	Primary key column name
fktable_qualifier	Foreign key table qualifier
fktable_owner	Foreign key table owner
fktable_name	Foreign table name
fkcolumn_name	Foreign key column name
key_seq	The column sequence number in the key, starting with 1
update_rule	not supported
delete_rule	not supported
fk_name	Name of the foreign key
pk_name	Name of the primary key

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.

Related Topics

SuiteAnalytics Connect System Tables

oa_tables

oa_columns

General Notices