• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 282
  • Last Modified:

PLSQL: foreign key in other tables?

as I can tell if a column is foreign key in other tables?
0
enrique_aeo
Asked:
enrique_aeo
  • 2
2 Solutions
 
slightwv (䄆 Netminder) Commented:
Check the view user_cons_columns.

Here is an example:
drop table tab2 purge;
drop table tab1 purge;

create table tab1(col1 number primary key);

create table tab2(col1 number, t1_col1 number,
constraint t2_fk foreign key(t1_col1) references tab1(col1));

select * from user_cons_columns where column_name='T1_COL1';

Open in new window

0
 
slightwv (䄆 Netminder) Commented:
The other view is user_constraints that will give you all the constraints on a table as well as the type.

Using the example above:
select constraint_name, constraint_type from user_constraints where table_name='TAB2';

Open in new window


Then you could also use user_cons_columns using the constraint name.  Once you get the name with the above query:
select * from user_cons_columns where constraint_name='T2_FK';

Open in new window


Or combine them with a join to get everything,
0
 
HuaMinChenSystem managerCommented:
Do you mean you want to adjust the column of Foreign key constraint? If yes, you must ensure the relevant values of FK columns do exist within the relevant master tables.
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now