how to display the columns affected by the constraints (primary and foreign keys)

userqiwk1 0
Viewed 18

For primary keys and foreign keys, how to display the columns affected by the constraints. And why are the columns concerned with primary and foreign keys visualized in a separate view of the data dictionary? Thanks

oracle-sqldeveloper oracle sql
2 Answers

I presume you'd want to query USER_CONSTRAINTS joined with USER_CONS_COLUMNS, e.g.

SQL> select a.constraint_name,
  2         a.constraint_type,
  3         b.column_name
  4  from user_cons_columns b join user_constraints a on a.constraint_name = b.constraint_name
  5  where a.table_name = 'EMPLOYEE';

CONSTRAINT_NAME                CONSTRAINT_TYPE      COLUMN_NAME
------------------------------ -------------------- --------------------
SYS_C00106596                  C                    EMPNO
PK_EMPLOYEE                    P                    EMPNO
FK_EMP_EMP                     R                    MGR

SQL>

As of your second question: what do you mean, separate view?

Littlefoot0

Open the table.

Go to the Constraints tab.

Select the constraint.

Look below at the Columns page.

enter image description here

Disclaimer: I work for Oracle and am a product manager for SQL Developer.

thatjeffsmith0
Related