Tag: constraints

Compound Keys Explained

Posted on by Ian

In SQL databases, a compound key is a type of primary key that consists of two or more columns combined to uniquely identify each row in a table. The key columns are used together as a single unit to ensure uniqueness.

Some within the SQL community define compound keys as composite primary keys comprising of foreign keys from other tables, so there doesn’t seem to be an agreed consensus on the precise definition.

With that in mind, let’s explore these definitions of compound keys.

Continue reading →

Understanding Self-Referencing Foreign Keys: A Beginner’s Tutorial

Posted on by Ian

In relational databases, a foreign key is typically a field that is linked to another table‘s primary key field in a relationship between two tables.

However, there’s also a type of foreign key we call the self-referencing foreign key. The self-referencing foreign key refers to a field within the same table, creating a relationship between rows in the same table.

Continue reading →

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 →