If you’re getting SQL Server error 107 that goes something like “The column prefix ‘e’ does not match with a table name or alias name used in the query“, look at your column prefix.
This error occurs when we use the asterisk wildcard (*) that’s incorrectly qualified with a column prefix. The prefix, if provided, must match a table name or alias name. If you’re using a table alias, then you must use that instead of the table name.
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 readingYou 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 readingIf 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 readingIf 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).
In PostgreSQL it’s possible to make an array larger without providing any values. For example, we can take an array with three elements, and enlarge it so that it contains say, five elements, with the two extra elements being NULL.
We can also do it so that some of the extra elements are non-NULL and others are NULL.
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 readingIndexes 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.
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.
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