Category: DBMS

Database Management Systems

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 →

“Incorrect syntax” Error When Creating a Filtered Index in SQL Server due to an Unsupported Operator

Posted on by Ian

If you’re getting the “Incorrect syntax…” error in SQL Server when trying to create a filtered index, it could be that you’re using an unsupported operator.

Filtered indexes in SQL Server only support simple comparison operators. If you use an unsupported operator or expression, you’ll likely get the dreaded “Incorrect syntax” error.

To fix, either use a supported operator, or create a view.

Continue reading →

How to Identify All Temporal Tables in Your SQL Server Database

Posted on by Ian

Temporal tables can be a powerful way to track historical data changes over time in our SQL Server databases. When we’re working with a database that uses temporal tables, we may need to identify all such tables quickly. Or we may simply want to check to see if it has any temporal tables.

Either way, the following SQL queries can be used to return a list of all temporal tables in the database.

Continue reading →

Fix Error “cannot truncate a table referenced in a foreign key constraint” in PostgreSQL

Posted on by Ian

When attempting to truncate a table in PostgreSQL, you might encounter the error “cannot truncate a table referenced in a foreign key constraint“. This is the default behaviour for the TRUNCATE statement whenever you try to truncate a table that is referenced by another table through a foreign key relationship.

If you want to truncate both tables, you can use the CASCADE option of the TRUNCATE statement. Alternatively, you could truncate both tables at the same time.

Continue reading →

2 Ways to Delete Rows from Parent and Child Tables in SQL Server when there’s a Foreign Key Relationship

Posted on by Ian

When working with relational databases, it’s common to encounter situations where you need to delete data from both parent and child tables that are linked by foreign key relationships. This article explores two methods for accomplishing this task in SQL Server, providing detailed examples and explanations for each approach.

Continue reading →

Using ON DELETE CASCADE When Creating a Foreign Key in SQL Server

Posted on by Ian

By default, if we try to delete a row in the parent table of a referential relationship in SQL Server, we’ll get an error. That’s because the default action is NO ACTION. This means that the delete doesn’t happen, and an error is raised.

But we’re not quite doomed yet. We can use the ON DELETE CASCADE option to ensure that the delete operation does happen, and that no error is returned. This option automatically deletes related records in the child table when a record in the parent table is deleted.

If we’re going to use this option, we need to define it when creating the foreign key. That’s because ON DELETE CASCADE is an optional argument that we can provide when creating the foreign key.

Continue reading →

Fix Error 1988 “Cannot rebuild clustered index … online because it is disabled” in SQL Server

Posted on by Ian

If you’re getting SQL Server error 1988 that reads something like “Cannot rebuild clustered index ‘PK__Employee__7AD04FF1A39ECAB1’ online because it is disabled.“, it’s probably because you’re trying to rebuild a disabled clustered index online.

We can’t rebuild a disabled clustered index with (ONLINE = ON).

Continue reading →

Create a Filtered Index in SQL Server

Posted on by Ian

In SQL Server, a filtered index is a special type of index that only includes a subset of rows from a table or view, based on specified filter criteria.

This criteria would match the criteria of some of the most commonly run queries on the table, thereby helping to improve the performance of those queries.

Below is an example of creating a filtered index in SQL Server.

Continue reading →