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 readingCategory: SQL Server
Understanding the ON DELETE SET DEFAULT Option in SQL Server
In 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 readingFix Error 1785 “Introducing FOREIGN KEY constraint … may cause cycles or multiple cascade paths” in SQL Server
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).
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 reading