Tag: constraints

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 →

What is a UNIQUE Constraint?

Posted on by Ian

A UNIQUE constraint is a rule we apply to one or more columns in a database table to ensure that the values in those columns are unique across all rows. This means that no two rows in the table can have the same value(s) in the column(s) where we’ve applied the UNIQUE constraint. It’s an essential tool for maintaining data integrity and preventing duplicate entries in our database.

Continue reading →

Fix Error 2788 “Synonyms are invalid in a schemabound object or a constraint expression” in SQL Server

Posted on by Ian

If you’re getting SQL Server error 2788 that reads “Synonyms are invalid in a schemabound object or a constraint expression” it seems that you’re trying (whether intentionally or not) to create a schemabound object that includes a synonym in its definition, or a constraint with a synonym in its expression.

Continue reading →

How to DROP Tables with Foreign Key Relationships in PostgreSQL

Posted on by Ian

Nothing pulls me out of the “zone” quicker than attempting to drop a table, only to be told “Nope, that table has a dependency!”.

Granted, this usually only happens in my development environments, as I rarely have any need to drop tables in a production environment.

And that’s probably why it “pulls me out of the zone”. I suddenly have to stop and start thinking about what tables contain foreign keys to the one I’m trying to drop. In dev environments, we’re often trying things out and so it’s not out of the question that we might inadvertently try to drop a table without realising it has dependent objects.

Fortunately, when we’re working with PostgreSQL, we have a quick and easy method to overcome this barrier.

Continue reading →

How to “Unhide” a GIPK in MySQL

Posted on by Ian

In MySQL, GIPKs are invisible by definition. GIPK stands for generated invisible primary key, and it’s basically an invisible column automatically created by MySQL with a primary key constraint.

However, just because GIPKs are automatically created invisible, it doesn’t mean that we can’t “unhide” them – or make them visible.

We can make a GIPK visible just as we would make any other invisible column visible – use the ALTER TABLE statement to set it to VISIBLE.

Continue reading →

Create a Generated Invisible Primary Key (GIPK) in MySQL

Posted on by Ian

MySQL 8.0.30 introduced generated invisible primary keys (GIPKs), which are primary keys that are automatically created whenever we create a table without explicitly defining a primary key.

GIPKs only work with the InnoDB storage engine, and they only work when we have GIPKs enabled.

In this article, I check whether or not GIPKs are enabled on my system, I then enable GIPKs, and finally I create a table with a GIPK.

Continue reading →

What is a Generated Invisible Primary Key (GIPK) in MySQL?

Posted on by Ian

While primary keys are generally considered a necessity when it comes to relational databases, they’re usually included in the SQL code that creates the database table.

MySQL 8.0.30 introduced generated invisible primary keys which provide an alternative to explicitly specifying a primary key for a table.

A generated invisible primary key (GIPK) is a primary key that’s created implicitly by the MySQL server. If we create a table without an explicit primary key, the MySQL server automatically creates a generated invisible primary key for us (assuming it’s an InnoDB table and that GIPKs are enabled).

Continue reading →