Oracle dictionary views that can be used are
1) user_constraints (This holds information about constraints available in current schema).
2) user_cons_columns (This holds information about columns available in current schema and also the constraint names that are associated to each individual column).
Refer for Oracle data dictionary views
Dictionary views in Prog. Findings part 1 |
Dictionary views in Prog. Findings part 2 |
Dictionary views in Prog. Findings part 3 |
Query:-
SELECT user_constraints.table_name AS chld_table_name,tab1.column_name AS chld_column_name FROM user_constraints,(SELECT column_name,constraint_name FROM
user_cons_columns) tab1
WHERE
tab1.constraint_name=user_constraints.constraint_name
AND
user_constraints.constraint_type='R'
AND
r_constraint_name IN (SELECT constraint_name FROM user_cons_columns WHERE table_name='[PARENT_TABLE_NAME INPUT]' AND column_name='[PARENT_TABLE COLUMN_NAME INPUT]')
Example:-
Consider 3 tables
1) User details table (Parent table).
2) Salary details table (Child table).
3) Leave details table (Child table).
 |
User details table has ID as primary key field which is been referenced by ID columns in Salary details and leave details table.
Upon running the above given query as
SELECT user_constraints.table_name AS chld_table_name,tab1.column_name AS chld_column_name FROM user_constraints,(SELECT column_name,constraint_name FROM
user_cons_columns) tab1
WHERE
tab1.constraint_name=user_constraints.constraint_name
AND
user_constraints.constraint_type='R'
AND
r_constraint_name IN (SELECT constraint_name FROM user_cons_columns WHERE table_name='USER_DETAILS' AND column_name='ID')
Output:-
 |
1 comment:
I try to find child's existence before allowing the user to delete a record (by enabling delete button).
This tutorial seems reflect what I'm trying to achieve but it's the query too slow.
Please explain how to know whether child exist for particular record without having to specify list of potential table to have child.
Post a Comment