SQL Atomicity Explained: The Basics of Atomic Operations

Posted on by Ian

One of the core principles that ensure data integrity in relational database management systems (RDBMSs) is atomicity. When dealing with SQL, understanding what “atomicity” and “atomic” means can help you design more reliable and robust databases.

Atomicity is a key concept that underpins how SQL transactions operate, ensuring that either all steps in a transaction are completed successfully, or none are. This article looks at the meaning of atomic in SQL, its importance, and how it fits into the broader context of database management.

Continue reading →

Understanding the ON DELETE SET DEFAULT Option in SQL Server

Posted on by Ian

In SQL Server, foreign keys are essential for maintaining referential integrity between tables. When setting up a foreign key, we have various options for handling actions when the referenced data is deleted. One of these options is ON DELETE SET DEFAULT.

This feature can be particularly useful in scenarios where it’s important to maintain the relationship while ensuring that the foreign key column doesn’t end up with invalid or null values.

Continue reading →

Fix Error 1785 “Introducing FOREIGN KEY constraint … may cause cycles or multiple cascade paths” in SQL Server

Posted on by Ian

If you’re getting SQL Server error 1785 that reads something like “Introducing FOREIGN KEY constraint ‘FK__Name’ on table ‘Department’ may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints“, it’s probably because you’ve specified the CASCADE option when trying to create a foreign key with a circular reference, or one that has multiple cascade paths (perhaps your child table has multiple foreign keys to the same primary key on the parent table).

Continue reading →

What is Domain Integrity?

Posted on by Ian

Domain integrity is an important concept in relational database management that ensures the accuracy, validity, and consistency of data within a database. It refers to the enforcement of rules and constraints that ensure data entered into a database adheres to a predefined set of acceptable values. This helps maintain the quality of data and prevents the entry of erroneous, incomplete, or invalid data into the system.

Continue reading →

Fix Error 1989 “Cannot enable foreign key constraint … as index … on referenced key is disabled” in SQL Server

Posted on by Ian

If you’re getting SQL Server error 1989 that reads something like “Cannot enable foreign key constraint ‘FK_Jobs_Employees’ as index ‘PK__Employee__7AD04FF1A39ECAB1’ on referenced key is disabled“, it’s probably because you’re trying to enable a foreign key, when the index that it references is disabled.

For example, if you’ve disabled a primary key on the parent table, and then try to enable a foreign key that references that primary key, then you’ll get this error.

To fix this issue, enable the index for the primary key/referenced key before enabling the foreign key.

Continue reading →

Create an Index in SQL Server

Posted on by Ian

Indexes play an important role in SQL database performance. We can use them to speed up commonly run queries, so that users don’t have to sit and wait for their results to come in.

In SQL Server, indexes can be created in several different situations. For example, when we create a primary key or a UNIQUE constraint, an index is created behind the scenes for us. However, we can also create indexes explicitly using the CREATE INDEX statement.

Continue reading →

Using ON DELETE SET NULL for Foreign Keys in SQL Server

Posted on by Ian

When creating a foreign key constraint in SQL Server, we have the option of including ON DELETE SET NULL in the definition. When we use this option in a foreign key, it tells SQL Server to automatically set the foreign key column values to NULL in the child table when the corresponding primary key record in the parent table is deleted.

Continue reading →

Fix Error 2714 “There is already an object named ‘…’ in the database” in SQL Server

Posted on by Ian

If you’re getting SQL Server error 2714 that reads something like “There is already an object named ‘Actors’ in the database” it seems that you’re trying to create an object that already exists.

It could be that you didn’t know that the object had already been created. Or it could be that you’re trying to create a different object, but you’re inadvertently using the same name that another object has. Or it could be that you want both objects to have the same name, but a different schema. In this case, perhaps you inadvertently omitted the schema name from the object.

Continue reading →

Understanding PostgreSQL’s FILTER Clause

Posted on by Ian

When using aggregation functions in PostgreSQL, we have the option of including a FILTER clause. This clause allows us to narrow down – or filter – the rows that are fed to the aggregate function.

This can be be a handy way to provide functionality that we might otherwise need to use a CASE statement or perhaps a CTE.

In this article, we’ll take a look at PostgreSQL’s FILTER clause, and see how it can simplify our SQL queries by replacing CASE statements with more concise code.

Continue reading →