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

Issue

This Content is from Stack Overflow. Question asked by Anthony

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



Solution

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?


This Question was asked in StackOverflow by Anthony and Answered by Littlefoot It is licensed under the terms of CC BY-SA 2.5. - CC BY-SA 3.0. - CC BY-SA 4.0.

people found this article helpful. What about you?