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).
Category: SQL Server
Fix Error 1989 “Cannot enable foreign key constraint … as index … on referenced key is disabled” in SQL Server
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 readingCreate an Index in SQL Server
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.
Using ON DELETE SET NULL for Foreign Keys in SQL Server
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.
Fix Error 2714 “There is already an object named ‘…’ in the database” in SQL Server
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
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 readingSome Handy T-SQL String Manipulation Techniques
Transact-SQL (T-SQL) offers a nice set of functions and methods for manipulating strings. Let’s look at some handy techniques that can help us work more efficiently with string data in SQL Server.
Continue readingHow to Identify All Temporal Tables in Your SQL Server Database
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 reading2 Ways to Delete Rows from Parent and Child Tables in SQL Server when there’s a Foreign Key Relationship
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 readingUsing ON DELETE CASCADE When Creating a Foreign Key in SQL Server
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.