Oracle foreign key analysis

Find Oracle foreign key by name
For Oracle error

SQL Error: ORA-02291: integrity constraint (SCHEMA_OWNER.FOREIGN_KEY_NAME) violated - parent key not found

Find the source and reference Oracle table of the foreign key name using the SQL query below

select uc.CONSTRAINT_NAME, 
  src_ucc.OWNER source_schema ,src_ucc.TABLE_NAME source_table, 
  src_ucc.COLUMN_NAME source_column, src_ucc.POSITION, 
  dest_ucc.TABLE_NAME reference_table, dest_ucc.COLUMN_NAME reference_column,
  dest_ucc.POSITION reference_position,
  uc.DELETE_RULE, uc.STATUS, uc.DEFERRABLE, uc.DEFERRED, uc.VALIDATED  
from sys.USER_CONSTRAINTS uc 
  join sys.USER_CONS_COLUMNS src_ucc 
  on uc.OWNER=src_ucc.OWNER and uc.CONSTRAINT_NAME=src_ucc.CONSTRAINT_NAME
  join sys.USER_CONS_COLUMNS dest_ucc 
  on uc.OWNER=dest_ucc.OWNER and uc.R_CONSTRAINT_NAME=dest_ucc.CONSTRAINT_NAME  
where uc.OWNER='SCHEMA_OWNER' and uc.CONSTRAINT_NAME='FOREIGN_KEY_NAME'
  and uc.CONSTRAINT_TYPE='R' 
  and src_ucc.POSITION=dest_ucc.POSITION
order by src_ucc.TABLE_NAME ,src_ucc.POSITION, dest_ucc.POSITION;

Find all Oracle foreign keys on a table
To find all the foreign keys that a Oracle table has, use the SQL query below

select uc.CONSTRAINT_NAME, 
  src_ucc.OWNER source_schema ,src_ucc.TABLE_NAME source_table, 
  src_ucc.COLUMN_NAME source_column, src_ucc.POSITION, 
  dest_ucc.TABLE_NAME reference_table, dest_ucc.COLUMN_NAME reference_column,
  dest_ucc.POSITION reference_position,
  uc.DELETE_RULE, uc.STATUS, uc.DEFERRABLE, uc.DEFERRED, uc.VALIDATED  
from sys.USER_CONSTRAINTS uc 
  join sys.USER_CONS_COLUMNS src_ucc 
  on uc.OWNER=src_ucc.OWNER and uc.CONSTRAINT_NAME=src_ucc.CONSTRAINT_NAME
  join sys.USER_CONS_COLUMNS dest_ucc 
  on uc.OWNER=dest_ucc.OWNER and uc.R_CONSTRAINT_NAME=dest_ucc.CONSTRAINT_NAME  
where src_ucc.OWNER='SCHEMA_OWNER' and src_ucc.TABLE_NAME='SOURCE_TABLE_NAME'
  and uc.CONSTRAINT_TYPE='R' 
  and src_ucc.POSITION=dest_ucc.POSITION
order by src_ucc.TABLE_NAME ,src_ucc.POSITION, dest_ucc.POSITION;

Find all Oracle foreign keys referenced to a table
To find all the Oracle tables that references a table via foreign key, use the SQL query below

select uc.CONSTRAINT_NAME, 
  src_ucc.OWNER source_schema ,src_ucc.TABLE_NAME source_table, 
  src_ucc.COLUMN_NAME source_column, src_ucc.POSITION, 
  dest_ucc.TABLE_NAME reference_table, dest_ucc.COLUMN_NAME reference_column,
  dest_ucc.POSITION reference_position,
  uc.DELETE_RULE, uc.STATUS, uc.DEFERRABLE, uc.DEFERRED, uc.VALIDATED  
from sys.USER_CONSTRAINTS uc 
  join sys.USER_CONS_COLUMNS src_ucc 
  on uc.OWNER=src_ucc.OWNER and uc.CONSTRAINT_NAME=src_ucc.CONSTRAINT_NAME
  join sys.USER_CONS_COLUMNS dest_ucc 
  on uc.OWNER=dest_ucc.OWNER and uc.R_CONSTRAINT_NAME=dest_ucc.CONSTRAINT_NAME  
where dest_ucc.OWNER='SCHEMA_OWNER' and dest_ucc.TABLE_NAME='REFERENCE_TABLE_NAME'
  and uc.CONSTRAINT_TYPE='R' 
  and src_ucc.POSITION=dest_ucc.POSITION
order by src_ucc.TABLE_NAME ,src_ucc.POSITION, dest_ucc.POSITION;

Column meaning

CONSTRAINT_NAME	The foreign key name
SOURCE_SCHEMA	The schema of the foreign key source table
SOURCE_TABLE	The table that has the foreign key
SOURCE_COLUMN	The column in the source table enforced by the foreign key
POSITION	For foreign keys with multiple columns, the order of the columns in the foreign key
REFERENCE_TABLE	The table referenced by the foreign key
REFERENCE_COLUMN	The column of the table referenced by the foreign key
REFERENCE_POSITION	For foreign keys with multiple columns, the order of the referenced columns in the foreign key
DELETE_RULE	The action Oracle performs with the row associated with the foreign key in the reference table is deleted NO ACTION: Oracle respond with a referential integrity error and will not allow the row to be removed CASCADE: Oracle automatically deletes the row in the tables with the associated foreign key SET NULL: Oracle automatically sets the foreign key column value to NULL
STATUS	Is the foreign key enforced ENABLED: The foreign key is enforced and active DISABLED: The foreign key is not enforced and inactive
DEFERRABLE	If a transaction can use SET CONSTRAINT[S] to dynamically defer a foreign key referential integrity check DEFERRABLE: Yes you can use SET CONSTRAINTS NOT DEFERRABLE: No you cannot use SET CONSTRAINTS
DEFERRED	If a foreign key is DEFERRABLE, when in the transaction will Oracle check for foreign key referential integrity violations by default. NOT DEFERRABLE is always IMMEDIATE. DEFERRED: Oracle will check when after a commit has been issued IMMEDIATELY Oracle will immediately check when the SQL is executed.
VALIDATED	When adding foreign keys to a Oracle table with existing data, checks to see if the existing data violates foreign key referential integrity. VALIDATED: Oracle will check to see if the existing data violates integrity. If it does then the foreign key will not be created NOT VALIDATED: Oracle will not check to see if the existing data violates integrity. The foreign key will be created even if there is existing bad data

See the Oracle language reference on constraints for more details.