title: unique by multiple columns and NULL values
body: <p>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:</p>
  
  <pre><code>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 }
  </code></pre>
  
  <p>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 <code>deleted_at</code> 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.</p>
  
  <p>Now coming to the <strong>actual problem</strong>: As column <code>deleted_at</code> 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.</p>
  
  <p>Now coming to the <strong>actual question</strong>: 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. <code>name = 'DELETED AT ' + deleted_at + ': ' + name</code>. 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 <code>deleted_at</code> back to NULL). The <code>deleted_at</code> 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?</p>
  
  <p>I thank you very much and am happy about your contributions.<br>
  Flinsch.</p>