Press "Enter" to skip to content

Category: Constraints

An Overview of Join Elimination

Published 2024-02-02 by Kevin Feasel

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

Published 2024-01-04 by Kevin Feasel

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

Published 2023-12-22 by Kevin Feasel

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

Published 2023-11-29 by Kevin Feasel

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

Published 2023-11-24 by Kevin Feasel

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

Published 2023-10-31 by Kevin Feasel

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

A Primer on Database Constraints in MySQL

Published 2023-09-08 by Kevin Feasel

Robert Sheldon creates some keys:

MySQL supports six basic types of constraints for ensuring data integrity: PRIMARY KEY, NOT NULL, DEFAULT, CHECK, UNIQUE, and FOREIGN KEY. In this article, I introduce you to each constraint type and provide examples for how they work. The examples include a series of CREATE TABLE statements that demonstrate different ways to incorporate constraints into your table definitions. If you’re not familiar with the CREATE TABLE statement or how to create tables in a MySQL database, refer to an earlier article in this series that introduces you to the statement.

In short, they support the same set that SQL Server users are used to. But do read on to see the nuances behind each of these.

Comments closed

Finding SQL Server Columns with Defaults

Published 2023-03-21 by Kevin Feasel

Tom Collins sticks to the defaults:

Do you have a sql query to check every  sql server database  column and identify if a default value is applied to the column?  

Click through for a script which does just that. Tom’s query goes against system views and there’s a separate way to get those details from sys.default_constraints if you prefer to have a second option. If you’re on an older version of SQL Server where CONCAT_WS() doesn’t exist, concatenate it yourself.

SELECT
	CONCAT_WS('.', QUOTENAME(OBJECT_SCHEMA_NAME(c.object_id)), QUOTENAME(OBJECT_NAME(c.object_id))) AS TableName,
	c.name AS ColumnName,
	dc.name AS DefaultConstraintName,
	dc.definition AS DefaultConstraintDefinition
FROM sys.default_constraints dc
	INNER JOIN sys.columns c
		ON dc.parent_object_id = c.object_id
		AND dc.parent_column_id = c.column_id;
Comments closed