# Adding a virtual unique key constraint in MySQL

Let's say in our system we track *owners* and their corresponding *cats*. 
```sql
| 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](https://media.giphy.com/media/VbnUQpnihPSIgIXuZv/giphy.gif align="center")

**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. 
```sql
| 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.
```sql
| 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.
```sql
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.
```sql
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.

```sql
| 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`.