Plenty of Performance, Power, and Foreign Keys

One more reason to use foreign key constraints

Since the title might be considered a bit vague, I don’t want you to wade through the article to figure it out. I will spare you the typical clickbait introduction, with me telling you what a foreign key constraint is, and why it and all the other constraint types provided by relational engines are useful. I will get straight to the point first: One more reason to use them is that they are very helpful when using a large language model AI (LLM), like Microsoft Copilot, currently in preview for Azure SQL.

If you are a relational database programmer, you have hopefully heard about foreign keys. They let you set up a relationship between one set of columns, to another set of columns. For example, if you have an invoice table with a customerId and a customer table with a primary key of customerId, a foreign key can establish a relationship between these two columns.

Since the primary topic of this discussion is the benefits for AI of using foreign key constraints, let’s ignore all the other values of foreign key constraints. The fact that they help the integrity of your data, can be useful in query optimization, and are at the very least, awesome documentation, let’s just ignore all of that and say none of that matters at all.

If you ask an LLM to join our hypothetical invoice table to an equally hypothetical customer table, the likely shared column of customerId would certainly be recognized as a likely way to join the two table. But what if your table has other shared column names. RowCreatedTime, Name, etc. How would it know that these too weren’t keys that needed to be joined on? It wouldn’t. And since there is a very common practice of naming the surrogate key ID in every table? It is not possible to have two ID columns in the same table. So, we typically end up with Invoice.CustomerId joining to Customer.Id.

But let’s be clear, not every table has even that level of clearly named columns. Let’s take a rather silly example of a couple of tables named T1 and T2. Their key columns are T11 and T21. In T11, there is a column T25 that references T11. Now, I did say this naming standard was silly, but this absolutely happens. And not always in as orderly a fashion as this. Sometimes table and column names may actually be something as “interesting” as a GUID (not the values, but the actual tables and columns). If you need to join T1 to T2, without a foreign key, you will 100% need to know the structure… and no LLM will likely ever be able to figure that out with any regularity. Certainly not in the amount of time you have hoped.

But if you have that foreign key in place… not only will your LLM be able to write T2 JOIN T1 on T2.T25 = T11; your programmers won’t be able to mess up your data and forget to only put values into T25 that exist in T11. Which is quite a feat because it was really hard just typing this and only needing to remember two awful column names. So don't forsake the wonderfully powerful foreign key constraint, if for no other reason than the joy of letting AI do some of the dirty work of joining tables for you in the future!

Louis Davidson (@drsql)

Join the debate, and respond to the editorial on the forums

Prompt engineering tools are software platforms that help business owners, content creators and prompt engineers craft effective prompts that maximize output from their large language models (LLMs) and generative AI tools

Artificial intelligence is here to change the world, and it is up to us to embrace this technology and use it responsibly to reap its full potential. Although critics...

Reading Time: 5 minutes In this post I want to share the results of my initial testing of the Microsoft Azure emissions insights capability within Microsoft Fabric. Which...

Digital media content creators have an ethical res...

It often surprises people to discover that confide...

The 2024 State of the Database Landscape survey showed that 79% of businesses are now using two or more database platforms (vs 62% in 2020). Businesses revealed that this growth was largely due to the differing use cases across each platform...

The tech giant’s business challenges have deservedly curbed its acceptance of political activism on company time, writes Dave Lee.

79% of us are now using two of more database platforms - fantastic for leveraging a range of benefits, but not so great when it comes to levels of complexity. Looking for ways to overcome this? Here are four steps to take towards multi-database simplicity.

PowerShell is an essential tool for SQL Server database administrators looking to streamline their workflow and automate repetitive tasks. When it comes to troubleshooting your SQL Server instances, PowerShell...

One of the major trends in enterprise computing, and really in enterprises themselves is an increased emphasis on data. My career has always revolved around data, but this is a new focus for many parts of the organization. Even business units that traditionally don’t care about data realize that access to more, and better, data can make their job easier or expand their capabilities.

In this article, I will discuss the history and thinking behind several types of logic that are typically associated with writing relational database code. Because of how relational databases implement NULLs, it definitely ...

In the previous article in this series, I demonstrated how to build and run an aggregate statement in MongoDB Shell. For the examples in that article, I used the version of MongoDB Shell that is embedded in MongoDB Compass. This article continues the discussion on aggregate statements, except that the focus is now on the graphical components built into the Compass interface.

Ryan Booz explains how recursive common table expressions work: The first two articles in this series demonstrated how PostgreSQL is a capable tool for ELT…

pg_tle - A Must-Know for DevelopersPostgreSQL...

The first two articles in this series demonstrated...

Cary Huang explains how row visibility works in Po...

Store cluster backup in another region. Restore cluster in the primary or another region.

My thanks to Jonathan Katz for his presentation, “Trusted Language Extensions for PostgreSQL”, at last week’s Postgres Extension Ecosystem Mini-Summit. As usual I’ve collected the transcript here interspersed with...

In the first article in this transforming data series, I discussed how powerful PostgreSQL can be in ingesting and transforming data for analysis. Over the last few decades, this was traditionally done with a methodology called Extract-Transform-Load (ETL) which usually requires external tools. The goal of ETL is to do the transformation work outside of the database and only import the final form of data that is needed for further analysis and reporting. However, as databases have improved and matured, there are more capabilities to do much of the raw data transformation inside of the database.

What Else Happens When Queries Try To Compile In S...

I’m kinda weird. I get excited when I’m troubleshooting a SQL Server problem, and I keep hitting walls.

I’ll give you an example. A client came to me because they were struggling with sporadic performance problems...

One Way Or Another The OR operator is a perfectly valid one to use in SQL statements. If you use an IN clause, there’s a reasonable chance that the...

Nikola Ilic reminds us to keep Ralph Kimball’s Data Warehouse Toolkit book at hand: But, what is a star schema in the first place? I…

Kurt Buhler performs comparative analytics: In a previous article about format strings, we showed an example of how format strings can improve visualizations. The visualizations in that…

Deborah Melkin continues a series on Power Apps: As a developer, I have two things I have to do: 1. Check code into source control…

TThis article describes how to compare time series that occur in different periods by standardizing the timelines to days since a specific event. ...

How to compare time series that occur in different periods by standardizing the timelines to days since a specific event...

Steven Sanderson searches for subsets: In data analysis, there often arises a need to extract the top N values within each group of a dataset.…

Steven Sanderson performs feature selection: As an R programmer, one of the fundamental tasks you’ll encounter is manipulating data frames. Whether you’re cleaning messy data…

Steven Sanderson wants to check for row existence:...

The company said a ransom was paid to protect pati...

Chad Callihan goes index hunting: SQL Server has the CHARINDEX() and PATINDEX() functions that can both be used to find the position of a value…

In today's data-driven world, SQL (Structured Quer...

Greg Low doesn’t need a fully-fledged instance of SQL Server: I saw a question on the Q&A forums about how to connect to the LocalDB…

Andy Brownsword digs into SSISDB: I’ve recently been reviewing SSIS packages to make some performance fixes and needed a way to validate the results of…

In a previous blog post we went through how to build a Docker container image from a remote (Github) repository. Here we’re going to expand on that by actually... The...