Tag: constraints

Fix Error 1989 “Cannot enable foreign key constraint … as index … on referenced key is disabled” in SQL Server

Posted on by Ian

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

Fix Error “cannot truncate a table referenced in a foreign key constraint” in PostgreSQL

Posted on by Ian

When attempting to truncate a table in PostgreSQL, you might encounter the error “cannot truncate a table referenced in a foreign key constraint“. This is the default behaviour for the TRUNCATE statement whenever you try to truncate a table that is referenced by another table through a foreign key relationship.

If you want to truncate both tables, you can use the CASCADE option of the TRUNCATE statement. Alternatively, you could truncate both tables at the same time.

Continue reading →

5 Ways to Return UNIQUE Constraints in SQL Server

Posted on by Ian

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.

Continue reading →

How to Disable a Primary Key in SQL Server

Posted on by Ian

Under most circumstances, disabling a primary key is a bad idea. A very bad idea. After all, we implement primary keys in the name of data integrity, and by disabling a primary key, we could compromise that effort.

But there may be cases where you need to disable a primary key, for one reason or another. For example, to facilitate data migration efforts, or bulk inserts, to perform certain maintenance tasks, or simply to insert dummy data in your development environment.

Whatever the reason, here’s how to disable a primary key in SQL Server.

Continue reading →

Fix Error 8111 “Cannot define PRIMARY KEY constraint on nullable column in table” in SQL Server

Posted on by Ian

If you’re getting SQL Server error 8111 that reads something like “Cannot define PRIMARY KEY constraint on nullable column in table ‘Employees’“, it’s probably because you’re trying to add a PRIMARY KEY constraint to an existing column that is nullable. In other words, the column isn’t defined with a NOT NULL constraint.

We can only add primary keys to columns that are defined as NOT NULL.

To fix this issue, define the table as NOT NULL before attempting to add the primary key.

Continue reading →

Fix Error 11415 “Object … cannot be disabled or enabled. This action applies only to foreign key and check constraints” in SQL Server

Posted on by Ian

If you’re getting SQL Server error 11415 that reads something like “Object ‘UQ_Employees_Email’ cannot be disabled or enabled. This action applies only to foreign key and check constraints“, it’s probably because you’re trying to disable either a DEFAULT, UNIQUE or PRIMARY KEY constraint.

Continue reading →

Fix Error 1987 “Cannot alter nonclustered index … because its clustered index is disabled” in SQL Server

Posted on by Ian

If you’re getting SQL Server error 1987 that reads something like “Cannot alter nonclustered index ‘UQ_Employees_Email’ on table ‘Employees’ because its clustered index is disabled“, it’s probably because you’re trying to rebuild a nonclustered index when the clustered index for the table is disabled.

To fix this issue, either enable/rebuild the table’s clustered index first, then try again, or enable all indexes at once.

The clustered index will typically be the primary key index (unless you’ve specified another clustered index for the table).

Continue reading →

Understanding Filtered Indexes in SQL

Posted on by Ian

In SQL, indexing can be a great way to improve query performance across our database. We can create indexes to support the most commonly used queries, and these indexes can help our queries run much faster.

But it’s not always a matter of just “adding an index” and leaving it at that. If we really want the best bang for our buck, we usually need to look at how best to implement the index. There are many decisions that go into designing the best index for the job. And one such decision might be, should this be a filtered index?

But what exactly is a filtered index, and how can it improve our database efficiency?

Let’s find out.

Continue reading →