0

How can tell the relationships between tables when no FKs are being used?

Just started working on a (partial) data export from Drupal MySQL database with ~1000 tables that contains hardly any foreign keys. Forgive my ignorance but I don't even know whether this is standard modus operandi in Drupal or some sort of anomaly. I have been expecting FKs as on most RDBS that I ever worked on FKs were present.

Entity API is being used extensively and so is data versionning if that makes any difference.

I know I could capture all the SQL queries hitting the database at runtime and then, assuming all the queries were run, I could tell how joins are made.

But looking for simpler way to tell relationships between tables similar to the one below.

enter image description here

  • 1
    I don't even know whether this is standard modus operandi in Drupal or some sort of anomaly It's standard MO - Drupal doesn't enforce any FKs at all. They can be supplied in hook_schema, but they're for documentation purposes only – Clive Jun 20 '18 at 15:19
  • Clive is correct. You basically just have to know what the foreign keys are. You would be better off building the content in Drupal, then exporting it in JSON or something. It's rarely a good idea to do anything directly with the Drupal database. – Jaypan Jun 20 '18 at 23:38
  • @Jaypan >you basically just have to know what the foreign keys are. Thanks for the tip but I am not building anything. Quite the contrary, attempting to export data from Drupal but see no way to figure out what the assumed FKs are other than checking the SQL queries at runtime which seems so lame. Drupal must have something better to figure out the relations, right? – matcheek Jun 20 '18 at 23:50
  • No, the relationships are maintained by core and/or the modules that create the tables. There are no foreign keys enforced by Drupal, though module maintainers can optionally add them if they want. Again, I'd ignore the database altogether. Create a script (or use a module) that builds the content, then export it as JSON or even .csv or whatever. – Jaypan Jun 20 '18 at 23:54
3

You have 3 possible options, but you most likely want to start with #2 and combine it with #3.

  1. Use php to print out the Entity Metadata Wrapper for various nodes objects of different types.
  2. Use the Devel Module and view the Node object
  3. Use Views

If the Entity definitions were properly implemented (a huge IF) it is possible to print out the relationships using entity_metadata_wrapper from php. You can even write it out to a file from command line using Drush but this will take some advanced skills. However, this typically results in tens of thousands of lines, an overwhelming amount of information, with many circular references.

Using the Devel Module will be much easier as you can simply click down into what the Node object is presenting. This may not show all the fields if they aren't filled out so you will want to create a test Node of each type with data in each field. If filled properly this may help relate structures when looking at the raw MySQL data as well.

Using Views, Drupal's built in query builder, will help you either understand the schema or just export it by adding the Views Data Export module. You will want to start from a perspective of particular Node type (a content type). Each Node type will show fields it can access and you should be able to build the queries from there. You may need to add Relationships (joins) to get all the data depending on how the developers built the system, its hard to say. Drupal will limit you to only the tables that are defined with Relationships.

You can see the MySQL Queries you are building at the bottom of the page if Show the SQL Queries is enabled in the Views settings tab (/admin/structure/views/settings).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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