SyntaxHighlighter JS

2016-03-06

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_NAMEThe 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.

No comments:

Post a Comment