Tag: how to

Aggregating Data in SQL with the GROUP BY and HAVING Clauses

Posted on by Ian

When writing queries in SQL, we sometimes need to summarize data based on specific criteria. Fortunately SQL makes this easy to achieve with the GROUP BY and HAVING clauses. These two clauses allow us to aggregate data and filter the results based on group conditions.

More specifically, GROUP BY allows us to group the results, and HAVING is an optional clause that we can use to filter the results based on certain conditions.

Below is an example of where we can use these two clauses to aggregate data and filter it based on certain criteria.

Continue reading →

Concatenate Array Elements into a String in PostgreSQL

Posted on by Ian

You may be aware that PostgreSQL has a couple of functions that allow us to concatenate strings. In particular, the concat() function allows us to concatenate multiple strings into one string, and the concat_ws() function allows us to do the same, but to also specify a separator for the concatenated strings.

But did you know that we have the ability to pass an array to these functions?

Continue reading →

Fix Error 547 “The DELETE statement conflicted with the REFERENCE constraint…” in SQL Server

Posted on by Ian

If you’re getting SQL Server error 547 that reads something like “The DELETE statement conflicted with the REFERENCE constraint “FK_Projects_DepartmentID”. The conflict occurred in database “test”, table “dbo.Projects”, column ‘DepartmentID’“, you’re probably trying to delete data from a parent table of a foreign key relationship.

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 →

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 →

“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 →