5

I'm not a DBA but I am a (mostly!) competent SQL report writer. Can I trouble you DBAs for some pro-knowledge, please?

I can connect SQL Developer to Oracle XE's data dictionary without any problem, i.e. view the information of the data dictionary as an ERD within SQL Developer that shows the tables belonging to HR and the the relationships between these tables expressed as links between the PKs and FKs of the individual tables.

However, when I import the data dictionary from my workplace's 11g Oracle DB into SQL Developer, it brings back all the tables of the schema I'm pointing SQL Developer at but doesn't show the relationships between these tables - i.e. no links between the PKs/FKs of the tables belonging to the schema.

Is this feature something that has to be written into the ERD manually, or should table relationship information be available as part of the tables that make up the data dictionary?

Improve this question
3
  • Are there constraints to be extracted? Check by running: SELECT * FROM user_constraints where constraint_type = 'R'
    – Chris Saxon
    Jun 22, 2012 at 14:26
  • Thanks for your reply. Apologies for answering this question but I couldn't see a 'feedback' option to comment on your comment. I just checked, no, there's a user_constraints table but it doesn't contain any information (at all). There is however the all_constraints table that does return information where constraint_type = 'R'. Is the issue that the all_constraints table belongs to system and the user_constraints belongs to my login to the schema therefore there's no link between the two at user-level meaning no relationships data is brought back? Thanks!!
    – user9631
    Jun 25, 2012 at 8:13
  • 1
    USER_CONSTRAINTS will show you information on constraints that your schema user owns. ALL_CONSTRAINTS will show you information about constraints that you have the appropriate permissions in place to see/use. DBA_CONSTRAINTS will show you all constraints.
    – Philᵀᴹ
    Jul 4, 2012 at 9:37

1 Answer 1

Reset to default
0

The data dictionary will only show what it can.

Users can design their schema with or without referential integrity defined in the actual database.

When you import a data dictionary, using SQL Developer, to a new or existing relational data model, the foreign key constraints will be shown by default - if they are there to begin with.

Now, specific to the tool, it CAN attemp to infer any relationships. It does this by looking for common column names sprinkled around your tables, like an 'XYZ_ID' column, that happens to be a PRIMARY KEY in a driving table.

enter image description here

I have step-by-step instructions for this here, on my blog.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.