Database Management Systems
If you’re getting an error that reads something like “Incorrect syntax near the keyword ‘ON’“, it appears that you’re trying to recreate an existing index, but you’re using the wrong syntax for the DROP_EXISTING argument.
The same error occurs when using the wrong syntax to set DROP_EXISTING to OFF; “Incorrect syntax near the keyword ‘OFF’“.
Either way, to fix this issue, be sure to use the right syntax.
Continue readingIf you’re getting SQL Server error 4145 that goes something like “An expression of non-boolean type specified in a context where a condition is expected…“, it’s probably because you’re using a boolean-like syntax in the wrong context.
This error occurs when a non-boolean expression is used in a context where a condition (i.e., something that evaluates to true or false) is expected. This often happens in IF statements, CASE expressions, or WHERE clauses when a value that isn’t a boolean is mistakenly used as a condition.
Even when we do use a boolean value, we can still get this error if we use it in the wrong context.
Continue readingWhen we have a column that’s defined as a composite type in PostgreSQL, we have some options when it comes to inserting data. For example, we can explicitly specify each individual field of the composite type, or we can use a row constructor to insert all fields at once.
Below are four different options for inserting composite values into a column in PostgreSQL.
Continue readingIf 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.
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 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 readingIn 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 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 reading