Press "Enter" to skip to content

Category: Constraints

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

Defending (Certain) Bad Practices

Published 2023-01-13 by Kevin Feasel

Aaron Bertrand considers the trade-offs:

For the first T-SQL Tuesday in 2023, Raul Gonzalez invites us to talk about cases where we have knowingly implemented worst practices.

Well, I have done it a lot. Most of the posts in my bad habits series are cautionary tales based on my own “learning the hard way.” There are always trade-offs with doing something correctly – maybe proper design is less efficient, or takes longer to write, or has to pass more checks. Over time, though, you start getting a feel for where it makes sense to cut these corners, and where it doesn’t.

Read on for some practical examples around Stack Overflow.

Comments closed

Deleting from Multiple Tables by Key

Published 2022-07-22 by Kevin Feasel

Guy Glantser needs to delete some data:

Sometimes you need to delete data from multiple tables in a database. For example, you might have a multi-tenant database, and you need to delete all the data that belong to some tenant.

The problem is that there are many tables in the database that contain data, which you need to delete. If you have a column like “TenantId” in all tables, then your life is easier, because you have a simple predicate to apply to your DELETE statements against all tables. But even then, if there are foreign keys between tables to enforce referential integrity, then things get more complicated.

Read through for Guy’s answer, which definitely works and can be the quickest solution. If you can’t drop foreign key constraints (even temporarily), I have a post from a while back on tracing foreign keys to “levels.” The post only covers finding the ordering but could be extended to delete data one level at a time.

Comments closed

Join Removal Due to Foreign Key Constraint

Published 2022-07-12 by Kevin Feasel

David Alcock shows a performance benefit from having a foreign key constraint in place:

Foreign keys are used in database design to enforce referential integrity but they also have some performance benefits as well that you might not necessarily notice unless you’re looking into your execution plans.

Let’s take the following query using the AdventureWorks2019 sample database where I’m selecting the BusinessEntityID and JobTitle from the HumanResources.Employee table and by using an inner join I’m only returning rows that have matching values (BusinessEntityID) in both tables:

There are specific rules to table elimination but if you meet the criteria, it can save you a bit of CPU time and I/O.

Comments closed