Welcome to my corner of the web
Adding a virtual unique key constraint in MySQL

Adding a virtual unique key constraint in MySQL

with support of soft deletions

Dennis Schmidt's photo
Dennis Schmidt
·

3 min read

Let's say in our system we track owners and their corresponding cats. 

| id | owner_id | cat_name   | is_deleted |
|----|----------|------------|------------|
| 1  | 100      | Puddy Tat  | 0          |
| 2  | 101      | Katy Purry | 0          |

We don't allow owners to give multiple cats the same name. But if one cat dies, the next cats can still have the same name. In order to enforce this rule we can add a virtual unique key. cat-meme

Allowed state: In this scenario we can see that owner_id=101 gave two cats the name Katy Purry. This is fine because one of them already passed away (is_deleted=1) so the next cat can have the same name. 

| id | owner_id | cat_name   | is_deleted |
|----|----------|------------|------------|
| 1  | 100      | Puddy Tat  | 0          |
| 2  | 101      | Katy Purry | 1          |
| 3  | 101      | Katy Purry | 0          | <- this insert is ok

Disallowed state: But at every point in time an owner can only have one cat with the same name.

| id | owner_id | cat_name   | is_deleted |
|----|----------|------------|------------|
| 1  | 100      | Puddy Tat  | 0          |
| 2  | 101      | Katy Purry | 0          |
| 3  | 101      | Katy Purry | 0          | <- this insert should fail

Table creation statement

We are adding a column virtual_uk and setting a UNIQUE KEY on this very column.

CREATE TABLE owner_cat (
    id int(11) unsigned auto_increment,
    owner_id INT(11) unsigned not null,
    cat_name VARCHAR(32) not null,
    is_deleted TINYINT(4) not null default 0,
    virtual_uk VARCHAR(64) GENERATED ALWAYS AS (if(is_deleted = 0, concat(owner_id, '_', cat_name), NULL)),
    PRIMARY KEY(id),
    UNIQUE KEY `uk` (virtual_uk)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Explanation: If is_deleted=0, this code will concat owner_id _ cat_name, otherwise it will set the column to NULL. If the column is set to NULL it will not violate our UNIQUE KEY constraint.

virtual_uk VARCHAR(64) GENERATED ALWAYS AS (
 if (is_deleted = 0
 ,CONCAT(owner_id, '_', cat_name)
 ,NULL)
)

If we are now inserting data into our table, it will look like this.

| id | owner_id | cat_name   | is_deleted | virtual_uk     |
|----|----------|------------|------------|----------------|
| 1  | 100      | Puddy Tat  | 0          | 100_Puddy_Tat  |
| 2  | 101      | Katy Purry | 1          | NULL           |
| 3  | 101      | Katy Purry | 1          | NULL           |
| 4  | 101      | Katy Purry | 0          | 101_Katy_Purry |

Conclusion

In this article we have learned how to create a virtual unique key, which allows us to enforce strict UNIQUE KEY constraints while still supporting soft deletions.

MySQL