Simple Foreign Key Detection

Friday, June 14, 2019

For better or worse, sometimes we omit foreign key constraints on columns with a foreign key relationship. Sometimes we do this for performance reasons, sometimes it’s the behavior of a framework we’re using, and sometimes it’s desirable semantically. Whatever the case, often times its handy to list the foreign keys in the database that aren’t constraints; and that’s where this simple tool we built comes in handy.

Finding Foreign Keys

Without further ado, here’s a tool for finding implicit foreign keys in a Postgres or MySQL database. It uses the Levenshtein distances of column names to find foreign key relationships. Obviously there are theoretically more sophisticated approaches to this problem (e.g. using the values of columns to match keys), but:

  • for the most part developers use sane naming conventions for their foreign keys, and we can take advantage of that,
  • more sophisticated approaches are prone to false positives because the domain of primary keys is often heavily overlapping across tables,
  • and, so far, in practice this approach has yielded decent results across a handful of databases.

Full documentation is in the README.md file in the repo.

Applications

There are a handful of situations where knowing a database’s foreign keys can be helpful. Our compelling use case is database subsetting. We have written about database subsetting, and released an open source subsetter. The summary is that database subsetting creates smaller versions of a database that are referentially intact and useful (it’s surprisingly easy to create a database that’s referentially intact, but not useful!). To create a referentially intact subset, you need to know where the references lie. Sometimes our customers don’t have foreign keys, and that’s where this tool is used.

Other applications include:

  • database migrations,
  • understanding a database you’re unfamiliar with,
  • recreating missing constraints.

What’s a Rich Text element?

The rich text element allows you to create and format headings, paragraphs, blockquotes, images, and video all in one place instead of having to add and format them individually. Just double-click and easily create content.

Static and dynamic content editing
Static and dynamic content editing

Static and dynamic content editing

A rich text element can be used with static or dynamic content. For static content, just drop it into any page and begin editing. For dynamic content, add a rich text field to any collection and then connect a rich text element to that field in the settings panel. Voila!

How to customize formatting for each rich text

Headings, paragraphs, blockquotes, figures, images, and figure captions can all be styled after a class is added to the rich text element using the "When inside of" nested selector system.