Skip to main content

Foreign Key References (Tickler)

 

Find All Foreign Key References

In my experience, a well-Normalized database winds up with lots of Foreign Key relationships between the tables. They are a real benefit, bringing so much ease and power in preserving data integrity. Best of all, they pass responsibility for maintaining that integrity to the RDBMS itself, rather than burying it in application code.

Looking at one table, it is relatively easy to see its list of Foreign Keys outward, to other tables.

Occasionally, though, we need to know what other tables have a Foreign Key defined for a given table. It is rare enough that I usually forget some of the little details in between uses. Rather than needing to do a web search every time, this Tickler post will help to jog my memory.

MySQL

-- what FKs reference this data?

SELECT TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE REFERENCED_TABLE_SCHEMA = 'MySchema'
AND REFERENCED_TABLE_NAME = 'MyTable';


These "memory tickler" posts are quick-hits to refresh my mind about little technical details that arise infrequently enough that I forget in between, and wind up doing an internet search every time. By posting them here, they will be easier to find as needed.)

Popular posts from this blog

How to do Git Rebase in Eclipse

This is an abbreviated version of a fuller post about Git Rebase in Eclipse. See the longer one here : One side-effect of merging Git branches is that it leaves a Merge commit. This can create a history view something like: The clutter of parallel lines shows the life spans of those local branches, and extra commits (nine in the above screen-shot, marked by the green arrows icon). Check out this extreme-case history:  http://agentdero.cachefly.net/unethicalblogger.com/images/branch_madness.jpeg Merge Commits show all the gory details of how the code base evolved. For some teams, that’s what they want or need, all the time. Others may find it unnecessarily long and cluttered. They prefer the history to tell the bigger story, and not dwell on tiny details like every trivial Merge-commit. Git Rebase offers us 2 benefits over Git Merge: First, Rebase allows us to clean up a set of local commits before pushing them to the shared, central repository. For ...

Git Reset in Eclipse

Using Git and the Eclipse IDE, you have a series of commits in your branch history, but need to back up to an earlier version. The Git Reset feature is a powerful tool with just a whiff of danger, and is accessible with just a couple clicks in Eclipse. In Eclipse, switch to the History view. In my example it shows a series of 3 changes, 3 separate committed versions of the Person file. After commit 6d5ef3e, the HEAD (shown), Index, and Working Directory all have the same version, Person 3.0.

Scala Collections: A Group of groupBy() Examples

Scala provides a rich Collections API. Let's look at the useful groupBy() function. What does groupBy() do? It takes a collection, assesses each item in that collection against a discriminator function, and returns a Map data structure. Each key in the returned map is a distinct result of the discriminator function, and the key's corresponding value is another collection which contains all elements of the original one that evaluate the same way against the discriminator function. So, for example, here is a collection of Strings: val sports = Seq ("baseball", "ice hockey", "football", "basketball", "110m hurdles", "field hockey") Running it through the Scala interpreter produces this output showing our value's definition: sports: Seq[String] = List(baseball, ice hockey, football, basketball, 110m hurdles, field hockey) We can group those sports names by, say, their first letter. To do so, we need a disc...

Updating Oracle javapath symlinks on Windows

A Java-based application on my Windows 10 machine recently started prompting me to upgrade my version of Java. Since I wanted to control it myself, I declined the app's offer to upgrade for me, and downloaded and installed the latest Java 8 from Oracle. In my case, Java 1.8.0_171, 64-bit version. The upgrade went fine. But when I launched the app, it again said I needed to upgrade. Why was it still looking at the old location? I made the change using Settings, to change the JAVA_HOME environment variable to point to the location of the new upgrade. But no change, the app still insisted that I needed to upgrade. A little research into the app's execution path showed that it was using c:\ProgramData\Oracle\Java\javapath to find Java. When I looked in that folder, I found symbolic links to my old Java installation. Normally, this hidden bit of information gets updated automatically in the upgrade or installation process. I have read of cases where, when downg...

Trigger Windows Scheduled Task from Remote Computer via Jenkins

One thing I love about working in Information Technology is the opportunity - the NEED - to constantly learn new things. If a week goes by in which I have not looked up something on StackOverflow or other message boards, I start lobbying my team for more challenges. This week, I learned the power of running " SCHTASKS.exe " from a command-line script for a remote server in a Microsoft Windows environment. If you don't know Schtasks, you can read up on it here: https://msdn.microsoft.com/en-us/library/windows/desktop/bb736357(v=vs.85).aspx In a nutshell, it is the command-line interface for the Windows Task Scheduler, and allows you (or a system administrator) to create, change, run, query, terminate, and delete scheduled tasks on a work-station, either the local one or a remote one. Not all of the features are available in older versions. In my scenario below, this was relevant as the local computer will be a Windows 8 machine, and the remote server is, shall we ...