Anand Buddhdev wrote:
On Fri, Feb 27, 2004 at 12:58:14PM +0200, Egor Egorov wrote:
I'm using mysql version 3.23.58, on Fedora core 1 (the default supplied on the system).
I have searched the mailing list archives, and google, for my query, but have not yet found an answer.
Does anyone know if it's possible to define 2 columns in a table, with a constraint that will ensure that values on both columns are unique? For example, if I have columns "a" and "b", then if I insert value "x" in column a, then I may not insert value "x" again in EITHER column "a" or column "b" again.
I'm trying to develop a structure for a table that will hold a user's primary email address, and an alias, and I'd like to have a column called "address" and a column called "alias", and of course, there must be no address or alias duplication. This allows addition and removal of an address and its alias in one insert, and if the insert fails, then we know there's duplication, and return an error message.
I have thought of other ways around this issue, but my ideal solution would be as above. If this is not possible, then I will go back to my other (IMHO less elegant) solutions.
No, you can't do in the above way. You can first check with SELECT statement if address or alias already exists.
Ok, thanks for the response.
The problem with first doing a select, and then an insert, is that there exists a race condition. Between the select and insert, someone else could insert a row which might cause a duplication.
I have another solution, in which I use a transaction to avoid this problem. But this means I have to change to mysql 4 or postgresql. Fedora core is still shipping mysql 3.23.58 :(
A few thoughts.
As you are no doubt aware, you can ensure that your addresses are unique by putting a unique index on the address column, and you can ensure that your aliases are unique by putting a unique index on the alias column. Unless you are working with only one domain , I assume your address column will hold the complete address (e.g. [EMAIL PROTECTED]), otherwise it couldn't be unique. Will you allow email addresses in the alias column? If not, it seems to me you can get the result you want. If your application logic makes sure the address is an address (e.g., it contains an @) and the alias is not an address (e.g., does not contain an @), then no alias could match an address, so the individual uniqueness contstraints should be sufficient.
====
Your requirement of no overlap between aliases and addresses implies that you plan to let the user enter either without specifying which. Presumably, you would then do something like:
SELECT * FROM usertable WHERE alias = 'user_input' OR address = 'user_input'
That's fine, but you should be aware that OR searches tend to be slow. There's a work-around using UNION, but that requires mysql 4. If you (your application) can tell the difference between an address and an alias (by having the user specify, or by checking for the presence/absence of @), however, you (your application) can then check only the relevant column, which will be fast due to the index. Something like
if 'user_input' contains '@' #it's an address SELECT * FROM usertable WHERE address = 'user_input' else # it's an alias SELECT * FROM usertable WHERE alias = 'user_input'
Michael
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
