ON UPDATE CASCADE is a referential integrity constraint option that we can use in SQL Server when creating foreign keys. It automatically updates foreign key values in child tables when the corresponding primary key in the parent table is updated.
Tag: how to
Fix “Cannot create index on view. It contains text, ntext, image, FILESTREAM or xml columns” in SQL Server (Error 1942)
If you’re getting an error that reads something like “Cannot create index on view ‘demo.dbo.v1’. It contains text, ntext, image, FILESTREAM or xml columns” in SQL Server, it appears that you’re trying to create an index on a view that uses an invalid data type.
Not all data types are supported when indexing views.
Continue readingHow to Truncate Tables with Foreign Key Relationships in PostgreSQL
When working with PostgreSQL databases, you may sometimes need to clear out all the data from tables that have foreign key relationships. This process, known as truncation, can be tricky when dealing with interconnected tables.
By default, if we try to truncate a table that is referenced by another table via a foreign key constraint, we’ll get an error that looks something like this: “ERROR: cannot truncate a table referenced in a foreign key constraint“.
You may have encountered this before finding this article. However, all is not lost. Below are two options for overcoming this issue.
Continue readingFix “The new name is already in use as a COLUMN name and would cause a duplicate that is not permitted” in SQL Server (Error 15335)
If you’re getting an error that reads something like “Error: The new name ‘c1’ is already in use as a COLUMN name and would cause a duplicate that is not permitted” in SQL Server, it appears that you’re trying to rename a column with a name that already exists in that table.
Basically, there’s already a column of that name in the table.
Continue readingFix “Cannot alter column because it is ‘COMPUTED'” in SQL Server (Error 4928)
If you’re getting an error that reads something like “Cannot alter column ‘c2’ because it is ‘COMPUTED’” in SQL Server, it looks like you’re trying to alter a computed column.
We can’t alter computed columns.
Continue readingFix “Cannot create index on view. The function yields nondeterministic results” in SQL Server
If you’re getting an error that reads something like “Cannot create index on view ‘demo.dbo.v1’. The function ‘sysdatetime’ yields nondeterministic results…” and so on, in SQL Server, it looks like you’re trying to create an index on a view that returns nondeterministic results.
Indexes can only be created on views that return deterministic results.
Continue readingHow to View your Current SET Options in SQL Server
SQL Server provides various SET options that control the behavior of our session. These options can impact query execution and results. Also, some features in SQL Server rely on certain SET options to be configured in a certain way (for example indexed views).
Getting the current SET options configuration is not as straightforward as one might think. We need to do a bit of trickery.
Let’s look at how to view our current SET options.
Fix “Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause” in SQL Server (Error 144)
If you’re getting an error that reads something like “Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause.” in SQL Server, it looks like you’re trying to use either an aggregate function or a subquery in the GROUP BY clause.
We can’t use aggregates or subqueries in the GROUP BY clause.
To fix this issue, remove any aggregate functions or subqueries from your GROUP BY clause.
Fix “Cannot create index on view because its select list does not include a proper use of COUNT_BIG” in SQL Server (Error 10138)
If you’re getting an error that reads something like “Cannot create index on view … because its select list does not include a proper use of COUNT_BIG…” and so on in SQL Server, it could be that you’re trying to create an index on a view that uses the GROUP BY clause, but doesn’t have the COUNT_BIG() function.
If the view contains a GROUP BY clause, then it must also have COUNT_BIG(*).
To fix this issue, try adding COUNT_BIG(*) to your SELECT list.
Fix Error 7999 “Could not find any index…” in SQL Server
If you’re getting SQL Server error 7999 that reads something like “Could not find any index named ‘IX_LastName’ for table ‘Employees’“, it appears that you’re trying to create an index with the DROP_EXISTING argument ON, but there is no existing index of that name.
When we set the DROP_EXISTING argument to ON, SQL Server tries to look for the index to drop before it recreates it with the new definition. If it doesn’t exist, then we get the error.