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 :( -- Anand Buddhdev Celtel International -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
