Let's say I have a table with the following fields:
primaryEmail | secondaryEmail
I know how to create a UNIQUE constraint on one of the fields, and I know how to create a UNIQUE constraint that combines both fields, but is there a way to ensure that no value exists twice, ACROSS both columns? For example, if there's a value of [email protected] in the primaryEmail field, I don't want it to be able to appear again in either the primaryEmail field OR the secondaryEmail field.
You might consider revising your data model and pulling the email address to another table and then relating the new and old tables together. Off the top of my head, something like this should work
create table master (
id int not null primary key,
name varchar(64)
);
create table email (
id int not null primary key,
address varchar(128) not null unique,
parent_id int not null,
type enum('prim', 'secd'),
foreign key (parent_id) references master(id)
on delete cascade,
unique (parent_id, type)
);
I don't love this design - I'm not a fan of the enum, for example - but it would solve your uniqueness constraint.
In my opinion, you would want to put two separate constraints on that field if that is really what you are trying to accomplish. What you are actually trying to do are two different things (make sure that column is unique within the record, and make sure that column within that row is also unique for the whole table).
