i created a table using mysql of users. columns of the table are sequence, email, date_created and date_canceled. primary is sequence and im trying to make a unique index for email and date_canceled - so when email is active (never been canceled - means date_canceled is NULL) there wont be another active email inserted or a situation of this happening.
i know its can be done with oracle db but with mysql unique indexs allow NULL.
any suggestions how to handle this?? thanks!
The BDB storage engine will treat NULL as a unique value, only allowing a single NULL value.
If a column that permits
NULLvalues has a unique index, only a singleNULLvalue is permitted. This differs from other storage engines, which permit multipleNULLvalues in unique indexes.
If you don't want to change your storage engine to get this behavior, your other options would be to change your table structure to store active e-mails and cancelled e-mails in different tables... create a trigger to enforce this behavior... or assign a magic date value for active e-mails, and no longer allow NULL values in this column.
My understanding of your question is that you want each user to have one active email associated with them at any time, also keeping a history of users' previous mails.
Solution 1
As of MySQL 5.7.5 you can do this by creating a generated column and then putting a unique constraint on that; http://mysqlserverteam.com/generated-columns-in-mysql-5-7-5/. e.g.
create table UniqueActiveEmailDemo
(
sequence bigint not null auto_increment primary key
, userId bigint not null
, email nvarchar(1024) not null
, date_created timestamp default now()
, date_cancelled datetime
, single_active_mail_per_user bigint as (if(date_cancelled is null, userId, null))
);
alter table UniqueActiveEmailDemo
add unique UK_UniqueActiveEmailDemo_SingleActiveMailPerUser
(single_active_mail_per_user);
Because MySQL allows multiple nulls in a unique constraint, where the record is cancelled, the generate column has a null value; so you can have as many of those records as you like. However if the record isn't cancelled, the generated column returns the user's id; this is subject to the unique constraint, so if there's another active record with the same user id the unique constraint will throw an exception.
Solution 2
Sql Fiddle: http://sqlfiddle.com/#!9/b54dce/2
However a cleaner method which also works with earlier versions would be to simply prevent date_cancelled from being nullable; instead setting it to a fixed value in the far future for any items not yet cancelled, then have the combination of user_id and date_cancelled be null; e.g.
create table UniqueActiveEmailDemo
(
sequence bigint not null auto_increment primary key
, userId bigint not null
, email nvarchar(1024) not null
, date_created timestamp default now()
, date_cancelled datetime not null default '9999-12-31 23:59:59'
);
ALTER TABLE UniqueActiveEmailDemo
ADD UNIQUE UK_UniqueActiveEmailDemo_SingleActiveMailPerUser
(userId, date_cancelled);
9999-12-31 23:59:59 as you had null values; i.e. whatever record has that value is active.active_from date and filter on where now() between date_active and date_cancelled; however you'd then need to add more checks to ensure that active windows for the same user didn't overlap; which further complicates things., date_cancelled datetime not null default '9999-12-31 23:59:59' check (date_cancelled = '9999-12-31 23:59:59' or date_cancelled <= now())