Anand Buddhdev <[EMAIL PROTECTED]> 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 :(
>
If you use MyISAM table type you can lock table with LOCK TABLES statement. Take a
look at:
http://www.mysql.com/doc/en/LOCK_TABLES.html
Transactional table InnoDB and BDB are available in 3.23.58 if MySQL server was
configured with --with-innodb/--with-bdb option.
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Egor Egorov
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net
<___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
