unique by multiple columns and NULL values
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 campai开发者_如何学Pythongn 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.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)
精彩评论