Tell me if this has ever happened to you: a user contacts support because they’re randomly seeing different order histories every time they log in. Sometimes they see their recent purchases, sometimes they see orders from years ago, and sometimes they see nothing at all. After several hours of debugging, you discover that they have three different accounts with the same email address. Each time they log in, your authentication system “randomly” picks one of the three accounts, so they get these weird results.

This nightmare scenario happens more often than you’d think, and it’s entirely preventable with proper database constraints.

How Duplicates Sneak In

Let’s look at how this happens. You have a simple users table and registration code that inserts their email and their password into the database.

INSERT INTO users (email, password)
VALUES ('alice@example.com', 'hashed_password_here');

That works fine. But what happens when someone fills out your registration form twice? It’s really common for people to double-click “submit” buttons in forms after all.

-- First request completes
INSERT INTO users (email, password)
VALUES ('alice@example.com', 'hashed_password_here');

-- Second request also completes (page refresh, double-click, etc.)
INSERT INTO users (email, password)
VALUES ('alice@example.com', 'hashed_password_here');

Now you have two accounts with the same email. When Alice logs in, your query is set up to return the first result:

SELECT * FROM users WHERE email = 'alice@example.com' LIMIT 1;

Now the question is which “Alice” does the database return? And with a log of technology-related topics, the answer is “it depends”. In this case, it depends on the query optimizer’s mood that day because, without an explicit “order by” statement, we don’t truly know what the first results will be. This inconsistency causes all sorts of problems like mismatched order histories or lost saved preferences, and always results in confused customers calling support.

Enter Unique Constraints

Thankfully, relational databases have been able to handle this by allowing us to define a unique constraint on a column (or group of columns) so we can’t add duplicate values.

This is done by using the ALTER TABLE or CREATE UNIQUE INDEX commands. For the ATLER TABLE command, we use the following structure:

ALTER TABLE [table] ADD UNIQUE INDEX [name_of_index] (comma_separated_list_of_columns);

We can add a unique constraint to the email column in our users table using the following.

ALTER TABLE users ADD UNIQUE INDEX users_email_unique (email);

Now duplicate inserts fail immediately with a constraint violation. Problem solved.

A Twist On The Issue

Now you have logic in your application that will prevent duplicate email addresses from existing in the users table. But what if you need to be able to support the user deactivating or deleting their account, but still allowing them to sign back up later? For example, your finance team doesn’t want to completely delete their information because they might still need to process a return.

To fix this, you can use soft deletions. When you soft delete data, you flag it as “deleted” or “inactive” using a deleted_at or is_active column to track the “old” user entries. In this example, we’ll use the deleted_at column, where a deleted_at column with a null value indicates the User is active, and a deleted_at column indicates the User has been deleted.

So when your user deletes their account, we run something like the following:

-- User requests account deletion
UPDATE users SET deleted_at = NOW() WHERE id = 123;

Now the original account still exists in the database with deleted_at set, and we’ve retained all the user’s transactions.

Now imagine that same user wants to come back and create a new account:

INSERT INTO users (email, password)
VALUES ('alice@example.com', 'new_hashed_password');
-- ERROR: Duplicate entry 'alice@example.com' for key 'users_email_unique'

The unique constraint blocks them because the old soft-deleted record still has that email. Your user is stuck, unable to re-register with their own email address. This creates a terrible user experience and generates more support tickets, or they might just give up and use your competitor.

PostgreSQL Solution: Partial Indexes

PostgreSQL provides an amazingly elegant solution to this problem with partial indexes. You can add a WHERE clause directly to your index definition.

CREATE UNIQUE INDEX users_email_unique ON users (email)
WHERE deleted_at IS NULL;

This index only includes rows where deleted_at IS NULL. Soft-deleted users are completely excluded from the uniqueness check. The syntax is cleaner, and the intent is immediately clear to anyone reading the schema.

MySQL/MariaDB Solution: Generated Columns

MySQL and MariaDB don’t support partial indexes directly, but you can achieve the same result using a generated column. A generated column is a column that you don’t enter, but instead the database engine “generates” the data for us.

We’ll do it using an ALTER TABLE command like the following:

ALTER TABLE users
ADD COLUMN not_archived BOOLEAN
GENERATED ALWAYS AS (IF(deleted_at IS NULL, 1, NULL)) VIRTUAL;

Now you have to create the index using both the email and not_archived columns.

CREATE UNIQUE INDEX users_email_active_unique ON users (email, not_archived);

This is a really cool trick, but it’s a little obtuse, so here’s how this works. The generated column not_archived evaluates to 1 when deleted_at IS NULL (meaning the user is active) and evaluates to NULL when the user has been soft-deleted.

Your unique constraint works here because MySQL allows multiple NULL values in a unique index. So when you soft-delete a user, their not_archived column becomes NULL, and the unique constraint ignores them.

Active users have not_archived = 1, so the constraint prevents duplicate active accounts with the same email. Deleted users have not_archived = NULL, so they don’t conflict with anyone.

Pretty cool.

Add an example here of the query failing

Multi-Tenant Applications: Composite Unique Constraints

Many SaaS applications need uniqueness within a tenant rather than globally. Each company using your platform should have unique user emails, but the same email can exist across different tenants.

CREATE UNIQUE INDEX users_tenant_email_active_unique
ON users (tenant_id, email, not_archived);

Now alice@example.com can exist as an active user in both Tenant A and Tenant B, but cannot have duplicate active accounts within the same tenant.

What You Need To Know

1. Unique constraints allow you to prevent duplicate data in a database table
2. Simple unique constraints break when combined with soft delete
3. MySQL/MariaDB requires a generated column that becomes NULL for deleted records, allowing the unique constraint to ignore them.
4. PostgreSQL partial indexes are cleaner, letting you add a WHERE clause directly to the unique constraint.