Marcin Marchwiany

Dev Notes

Unique ID and soft delete.

2020-03-21

Let's consider a database table that stores organizations. Each organization should be uniquely identified by a code, and we want to ensure that no duplicate organizations exist.

Initial Table Structure

A standard way to enforce uniqueness is by creating a unique index on the code column: 

CREATE TABLE `organizations` (
    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(255) NOT NULL,
    `code` VARCHAR(8) NOT NULL,
    `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE INDEX `UK_organizations_code` (`code`)
);

Implementing a Soft Delete Mechanism

To allow soft deletion, we introduce a deleted_at column, which stores the timestamp when a record is marked as deleted. If an organization is active, this column remains NULL. 

CREATE TABLE `organizations` (
    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(255) NOT NULL,
    `code` VARCHAR(8) NOT NULL,
    `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    `deleted_at` TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE INDEX `UK_organizations_code` (`code`)
);

The Problem with Unique Constraints and Soft Deletion

Suppose we insert an organization with code = 'ORG1'. Later, this organization is marked as deleted by setting deleted_at to a timestamp. If we then try to insert a new organization with the same code, the unique index constraint will prevent it.

Solution: Conditional Uniqueness

We want to enforce uniqueness only for active (non-deleted) organizations. To achieve this, we modify the unique index to consider both code and deleted_at: 

CREATE TABLE `organizations` (
    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(255) NOT NULL,
    `code` VARCHAR(8) NOT NULL,
    `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    `deleted_at` TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE INDEX `UK_organizations_code` (`code`, `deleted_at`)
);

How This Works

For active organizations, deleted_at is NULL, so the unique constraint ensures that only one active record exists per code.

When an organization is soft-deleted, deleted_at is set to a timestamp, making the (code, deleted_at) pair unique.

This allows a new organization with the same code to be inserted with deleted_at = NULL, since its (code, NULL) pair does not conflict with any existing record.

Example Scenario

id name code deleted_at
1 Org A ORG1 NULL
2 Org A (old) ORG1 2024-01-01 10:00:00

This approach allows reusing organization codes after deletion while maintaining uniqueness among active records.

back