unique by multiple columns and NULL values

Question

I'm developing using symfony 1.4 (and Doctrine) and have a MySQL database table with an unique index on multiple columns. First, the YAML definition of the table so far:

Campaign:
  actAs:
    Sluggable:
      fields: [name]
      canUpdate: true
      uniqueBy: [merchant_id, deleted_at]
    Timestampable: ~
    SoftDelete: ~
  columns:
    merchant_id:      { type: integer, notnull: true }
    name:             { type: string(255), notnull: true, notblank: true }
    start_date:       { type: date, notnull: true, notblank: true }
    end_date:         { type: date, notnull: true, notblank: true }
  indexes:
    unique_name:  { fields: [name, merchant_id, deleted_at], type: unique }
  relations:
    Merchant: { local: merchant_id, foreign: id }

As you can see, I have to deal with campaigns belonging to merchants. A campaign knows its merchant and has a name (as well as a start date and an end date). The name of a campaign should be unique -- not globally but for that specific merchant. Up to here, I would need a unique index on the campaign name and the respective merchant. But, as the table "acts as SoftDelete" and the user should be able to create a new campaign with a name that already exists for a "soft-deleted" campaign, the deleted_at column also has to be part of the unique index. You see, the uniqueness of a campaign's name concerns only not deleted campaigns of the respective merchant.

Now coming to the actual problem: As column deleted_at is NULL for all not deleted campaigns and NULL values within a unique index are always treated as being unique, all campaigns are allowed to have non-unique names -- in the true sense. I know, this applies for MyISAM and InnoDB tables but not for BDB tables. However, switching to BDB is not my favourite option, if you know what I mean.

Now coming to the actual question: What are other possible options in addition to changing the MySQL engine to BDB? A workaround could be to rename a campaign that is soft-deleted, e.g. name = 'DELETED AT ' + deleted_at + ': ' + name. This, on the one hand, would have the advantage that all soft-deleted campaigns would expectedly have unique names even in the case in which they are restored (resetting deleted_at back to NULL). The deleted_at column wouldn't have to be part of the unique index any longer and, thus, all campaigns (not deleted, soft-deleted as well as restored once) would have a unique name -- concerning the respective merchant. But, on the other hand, I don't think this would be the most elegant solution. What are your opinions and expertise on this?

I thank you very much and am happy about your contributions.
Flinsch.

Answer 1

I think you can keep your basic structure, you just need a way to make deleted_at NOT NULL. That means you'll need to give it a default. A good default to use is 0, or 0000-00-00 00:00:00.

My recommendation is to add a new column to flag whether the rows is logically deleted. You can call it "IS_DELETED". Then add the default for deleted_at and make it non-null, and include is_deleted in your unique index.

Here's a very simple example of this approach in action:

mysql> create table merchant(
    -> id int unsigned not null auto_increment,
    -> name varchar(50) not null, 
    -> is_deleted tinyint not null default 0, 
    -> deleted_at datetime not null default 0,
    -> primary key (id),
    -> unique key name_and_deleted_at (name,is_deleted,deleted_at)
    -> ) ENGINE = InnoDB;
Query OK, 0 rows affected (0.08 sec)

mysql> 
mysql> -- successful inserts
mysql> insert into merchant (name,is_deleted) values ('foo',0);
Query OK, 1 row affected (0.00 sec)

mysql> insert into merchant (name,is_deleted) values ('bar',0);
Query OK, 1 row affected (0.00 sec)

mysql> 
mysql> -- insert failure due to duplicate name
mysql> insert into merchant (name,is_deleted) values ('foo',0);
ERROR 1062 (23000): Duplicate entry 'foo-0-0000-00-00 00:00:00' for key 'name_and_deleted_at'
mysql> -- logical delete
mysql> update merchant set is_deleted = true, deleted_at = now() where name = 'foo';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> -- now the insert succeeds
mysql> insert into merchant (name,is_deleted) values ('foo',0);
Query OK, 1 row affected (0.01 sec)

mysql> 
mysql> -- show data
mysql> select id,name,is_deleted,deleted_at 
    -> from merchant
    -> order by id;
+----+------+------------+---------------------+
| id | name | is_deleted | deleted_at          |
+----+------+------------+---------------------+
|  1 | foo  |          1 | 2010-11-05 13:54:17 |
|  2 | bar  |          0 | 0000-00-00 00:00:00 |
|  4 | foo  |          0 | 0000-00-00 00:00:00 |
+----+------+------------+---------------------+
3 rows in set (0.00 sec)