Using ON DELETE CASCADE When Creating a Foreign Key in SQL Server

Posted on by Ian

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.

Continue reading →

Fix Error 1988 “Cannot rebuild clustered index … online because it is disabled” in SQL Server

Posted on by Ian

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).

Continue reading →

Create a Filtered Index in SQL Server

Posted on by Ian

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 reading →

SQL CASE Examples with Multiple Conditions

Posted on by Ian

The SQL CASE statement is a handy tool that allows us to add conditional logic to our queries. It’s particularly useful when we need to categorize or transform data based on multiple conditions.

In this article, we’ll explore how to use the CASE statement with multiple conditions, providing simple examples that should work across most major relational database management systems (RDBMSs).

Continue reading →

Fix Error Msg 129 “Fillfactor 0 is not a valid percentage; fillfactor must be between 1 and 100” in SQL Server

Posted on by Ian

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 reading →

Fix Error 1913 “The operation failed because an index or statistics with name … already exists on table” in SQL Server

Posted on by Ian

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 reading →

Create an ENUM Type in PostgreSQL

Posted on by Ian

Some RDBMSs support an enum type, which comprise a static, ordered set of values. Also known as enumerated types, enum types can be handy for making a column accept just a small set of values, such as days in the week, predefined clothing sizes, or any number of other preset values.

In PostgreSQL, if we want to create a table that uses an enum type, we need to create the enum type first, then apply it against the table. This is a bit different to other RDBMSs such as MySQL, where we don’t need to create the enum type first.

Continue reading →