Recently, I stumbled on a pretty simple problem that usually solved in over-complicated ways. There was a need for a cross table constraint. The relation between those tables was one-to-many. That’s why combining them was not an option. Also, it would incorrectly represent the relationship between entities.
I think the simplest way to explain the problem and the solution is to do it via an example. The example that I picked up is an artificial one and it is much simpler than the problem that I was facing. Because of the example’s simplicity, we can concentrate on a single problem.
We have a database with records of companies in different countries and brands that they are using. Let’s list obvious constraints:
- A company should be unique per country
- A brand should be uniquely assigned to a single company in a single country
And already, there is a modelling error. We can have one brand assigned to multiple companies in the same country, which shouldn’t be a case.
The answers on the internet were not satisfying at all:
- Merge tables
- Copy the country data to both table
- Create a trigger that will be executed before insert into the brands table
I don’t want to describe in details why these approaches are not perfect. I was looking for something simple, flexible and in some way more idiomatic.
I was looking into CHECK constraints. But it could work only with columns inside the table, at least it was what I thought it can only do. To my surprise CHECK also can work with functions. In the end, CHECK just needs an expression that returns boolean. And it was the aha moment for me.
We will need to create a function that checks that a brand is uniquely assigned per country.
Let’s try to run the same queries:
After fixing the insert query into brands by using a unique brand for company_2, let’s try to apply it.
There are no errors caused by a constraint violation. The solution is quite simple and preserves correct modelling. But also, in general, CHECK provides a good interface to build much more sophisticated checks than usual constraints can provide.
