The case for native assertions in PostgreSQL
The original author of this query probably intended to write a query that returns a list of user accounts with their associated HubSpot account. However, we can infer that the query assumes that there is a one-to-one relationship between user accounts and HubSpot accounts. However, even if that assumption is true at the time of writing, it might not be true in the future.
Let's say that the hubspot_account table records one-to-one relationships between user accounts and HubSpot accounts. This might be enforced by a unique constraint on the hubspot_account.user_account_id column. In this case, the query above will return one row for each user account, as expected. However, that constraint might be removed in the future, and the query will start returning a random HubSpot account for each user account. This would lead to subtle bugs that are hard to catch.
Because of reasons discussed in this article, the above pattern is not recommended. Instead, you should add a column to the user_account table that stores the foreign key to the hubspot_account table. This way, you can enforce the one-to-one relationship at the database level.
