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 readingCategory: SQL Server
Using 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.
Fix Error 1988 “Cannot rebuild clustered index … online because it is disabled” in SQL Server
If you’re getting SQL Server error 1988 that reads something like “Cannot rebuild clustered index ‘PK__Employee__7AD04FF1A39ECAB1’ online because it is disabled.“, it’s probably because you’re trying to rebuild a disabled clustered index online.
We can’t rebuild a disabled clustered index with (ONLINE = ON).
Create a Filtered Index in SQL Server
In SQL Server, a filtered index is a special type of index that only includes a subset of rows from a table or view, based on specified filter criteria.
This criteria would match the criteria of some of the most commonly run queries on the table, thereby helping to improve the performance of those queries.
Below is an example of creating a filtered index in SQL Server.
Continue readingFix Error Msg 129 “Fillfactor 0 is not a valid percentage; fillfactor must be between 1 and 100” in SQL Server
If you’re getting SQL Server error Msg 129 that reads “Fillfactor 0 is not a valid percentage; fillfactor must be between 1 and 100“, it appears that you’re trying to set the fillfactor of an index to zero.
While it’s true that zero is the default setting, we can’t actually explicitly set the fillfactor to zero when creating or rebuilding an index.
But all is not lost. Below are two solutions to this problem.
Continue readingHow to Enable a Primary Key in SQL Server
If you’ve got a primary key in SQL Server that you need to enable, you’ll need to enable its index. The index is how we enable and disable primary key constraints in SQL Server.
More specifically, we enable a primary key index (and other indexes) by rebuilding them.
Below is an example of enabling a primary key in SQL Server.
Continue readingFix Error 1913 “The operation failed because an index or statistics with name … already exists on table” in SQL Server
If you’re getting SQL Server error 1913 that reads something like “The operation failed because an index or statistics with name ‘IX_Employees_HireDate’ already exists on table ‘Employees’“, it appears that you’re trying to create an index with the same name of one that already exists.
In SQL Server, index names must be unique within a table or view, although they don’t have to be unique within a database. Also, index names must follow the rules of identifiers.
Continue readingFix Error “The configuration option ‘fill factor’ does not exist, or it may be an advanced option” in SQL Server
If you’re getting an error that reads “The configuration option ‘fill factor’ does not exist, or it may be an advanced option” in SQL Server, it appears that you’re trying to view or set the default fill factor configuration option.
In SQL Server, fill factor is considered an advanced configuration option. By default, advanced options aren’t available for viewing and changing. However, we can use the following technique to make them available.
Understanding Fillfactor in SQL Server
One of the various options we have when creating or rebuilding indexes in SQL Server is specifying a fillfactor. If we create or rebuild an index without specifying a fillfactor, then the default fillfactor is applied.
In some cases, using the default fillfactor may be fine, even ideal. In other cases it could be less than ideal, even terrible.
Let’s look at what fillfactor is, how it works, and how we can use it to enhance database performance.
Continue reading5 Ways to Return UNIQUE Constraints in SQL Server
In SQL Server, we can use UNIQUE constraints to ensure that a column (or columns) contain only unique values. When we have a UNIQUE constraint against a column, the system will prevent any duplicate values are entered into that column.
Sometimes we need to return a list of UNIQUE constraints, so that we simply know what we’re working with. Other times we might want to create scripts for all of our UNIQUE constraints so that we can recreate the constraints later.
Regardless of the reason, here are five methods for returning UNIQUE constraints in a SQL Server database.