Press "Enter" to skip to content

Category: Constraints

The Power of the Check Constraint

Joe Celko defends the honor of the check constraint:

The problem is that newer SQL programmers (and even some more experienced ones) don’t take advantage of these constraints. Consider this slight rewrite of an actual posting on an SQL forum. In the original, the poster thought that identifiers should be integers and that ISO–11179 naming rules do not apply, so I cleaned it up a little, but this will make my point.

Click through to learn more about check constraints.

Comments closed

Contrasting Three Unique Identifiers in Postgres

Laetitia Avrot shares some advice:

This month’s PGSQLPhriday event is about UUId thanks to my post calling for a fight debate on the topic.

I will answer a question a friend developer asked me: “What is the best when we need a primary key? UUID, CUID, or TSID?”

I hadn’t heard of two of these, but Laetitia provides some links to learn more about them and then offers up some advice on whether to use any of them. And the advice sounds a lot like the advice for SQL Server.

Comments closed

An Overview of Join Elimination

Chad Callihan doesn’t need solo rows; he needs team players:

SQL Server can be pretty smart when it comes to avoiding work it’s not required to do. One instance of this is the concept of join elimination. Join elimination occurs when queries involving joins may only need to check one table instead of two (or more) based on foreign key constraints.

Let’s take a look at join elimination in action.

Read on for one example of join elimination and one more reason why those foreign key constraints are so useful.

Comments closed

Foreign Key Discovery in SQL Server & Azure SQL DB

Josephine Bush walks around town with a lantern looking for a good foreign key:

There are plenty of times I’m called upon to fix data. To do this, I must know what dependencies are in the database. Foreign keys are a crucial aspect of maintaining data integrity within relational databases. They establish relationships between tables, ensuring data references remain consistent and accurate. In an Azure SQL Database, identifying and managing foreign keys is essential for maintaining a well-structured and reliable database architecture.

Click through for a primer on foreign key constraints, a few ways to find them, and some closing thoughts on working with tables containing foreign key constraints.

Comments closed

The Value of Indexing Foreign Key Columns

Etienne Lopes takes us through a scenario:

Let me start this post with a question, “Do you think that it can be beneficial to have a single column index for the foreign key column in the child table?

Well, I believe I can ear three types of answers to this question:

  • Always!
  • Never!
  • It Depends…

Click through for Etienne’s answer. I’d still prefer these indexes to have multiple uses, which generally means having enough columns on the index to act as a covering index for one or more important queries. But Etienne does show a good use case for this single-column index.

Comments closed

Constraints in Microsoft Fabric Data Warehouses

Brian Bønk slips out of the constraints:

When working with data and building data models, I personally seldom use the constraints feature on a database. Call me lazy – but I think constraints are adding unnessesary complexity when building data models for reporting. Especially if you are working with the some of new platforms – like Microsoft Fabric, where you are using staleless compute, aka. data storage is seperated from the compute layer.

I understand the need for contraints on other database systems like OLTP systems.

In reporting models it can be somewhat usefull to have constraints between tables, as they help/force you to some level of governance in your datamodel.

But how can we use this in Microsoft Fabric and are they easy to work with?

Read on for those answers. I will note that I’m a stickler about constraints in transactional systems, though I agree that constraints in warehouses are not critical—assuming, at least, that you’re following the Kimball approach and have one and only one mechanism to write data, and that you have other mechanisms for vetting data quality.

Comments closed

Key Constraints in Databricks Unity Catalog

Meagan Longoria gives us a warning:

I’ve been building lakehouses using Databricks Unity catalog for a couple of clients. Overall, I like the technology, but there are a few things to get used to. This includes the fact that primary key and foreign key constraints are informational only and not enforced.

If you come from a relational database background, this unenforced constraint may bother you a bit as you may be used to enforcing it to help with referential integrity. 

Read on to see what is available and why it can nonetheless be useful in some circumstances.

Comments closed

Adding a Foreign Key while Creating a Table

Steve Jones points out one of the changes to T-SQL I really like:

This assumes I’ve added a table called dbo.Order with a PK of OrderID.

However, I can do this in the CREATE TABLE statement, like shown below. I add a new section after a column with the CONSTRAINT keyword. Then I name the constraint, which is always a good practice. I can then add the FK keyword, the column and the references that connects this child column to the parent column.

This came about in SQL Server 2014, along with In-Memory OLTP and the ability to create indexes inline with the table create script. It’s a minor quality of life thing but I do enjoy it.

Comments closed