Press "Enter" to skip to content

Category: Constraints

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

The Importance of Naming Constraints in SQL Server

Published 2023-03-15 by Kevin Feasel

Eitan Blumin gives everything a name:

This article was published by Aaron Bertrand a few years ago, talking about system-named constraints in SQL Server.

The article mostly focuses on the issue of naming conventions as the main issue with system-named constraints and provides a useful stored procedure script to generate sp_rename commands for all system-named constraints.

However, the script in the article provides the solution for only one database and doesn’t support the new “Edge Constraints” that were introduced in SQL Server 2019.

Check out Aaron’s article and Eitan’s follow-up piece.

Comments closed

Database Constraints in Postgres

Published 2023-02-03 by Kevin Feasel

Grant Fritchey does some data modeling:

PostgreSQL supports constraints much like any other database management system. When you need to ensure certain behaviors of the data, you can put these constraints to work. I’ve already used several of these in creating my sample database (available articles publicly on GitHub, in the CreateDatabase.sql file). I’ll explain those as we go. The constraints supported by PostgreSQL are:

Read on for the list, which includes one constraint which doesn’t have a direct analog in SQL Server.

Comments closed