MySQL unique behavior on NULL columns

I have this table

CREATE TABLE IF NOT EXISTS `profile_connections开发者_运维百科` (
  `id` int(11) NOT NULL auto_increment,
  `profile_id` int(11) NOT NULL,
  `connection_profile_id` int(11) NOT NULL,
  `created` timestamp NOT NULL default CURRENT_TIMESTAMP,
  `deleted` timestamp NULL default NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `profile_id_2` (`profile_id`,`connection_profile_id`,`deleted`),
  KEY `profile_id` (`profile_id`,`connection_profile_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;

I have a UNIQUE on my columns (profile_id, connection_profile_id, deleted)

Now the would like to be able to use INSERT IGNORE INTO profile_connections SET profile_id = 2, connection_profile_id = 3

And i want the database to "ignore" the insert if a row exists already with the values profile_id = 2, connection_profile_id = 3 and deleted = null

But the table dont see the "deleted = null" as being the same values, so multiple rows can exists with the null value.

Like

id | profile_id | connection_profile_id | created             | deleted
2  | 4          | 3                 | 2010-12-16 10:34:46 | NULL
3  | 4          | 3                 | 2010-12-16 10:34:53 | NULL

Is there anyhow to make the table work as i would like. Or is the only way to create a SELECT query first to see if a row already exists??

It may not be what you want to do, but the obvious solution is to not use a NULLable column. Either create a separate deleted column which is a boolean/tinyint to with false/0 to indicate the row is not deleted, or use a default time in the deleted timestamp for non deleted rows.

Apparently, this is by design in InnoDB. Here's an answer: http://forums.mysql.com/read.php?22,53591,53975#msg-53975.

I believe they suggest you could use a custom value (like 999999999) instead of NULL. Or maybe -1?