SEO

#1 (**permalink**) 02-28-2008, 10:27 AM

Hi all,

I have a table where I need entries to be unique across two columns.
ie:

NAME UNIQUE
john true
john false
john false

I can have as many john false's as I like, but only ever one john
true.

I need to prevent two people inserting a john true at the same time.
As far as I can see, the unique contraint can only be applied to a
single column so I can't use the built in stuff.

It appears to my newbie eyes that the only way I can do this is with a
table write lock. ie:
Lock the table for writes
Do a SELECT to see if the unique entry is already in there.
Insert it if it isn't
Unlock the table.

Are there any quicker / more efficient ways? Also I'm a bit unsure of
the syntax to only insert if the select comes up empty. Can anyone
point me in the right direction?

Many thanks,
Lister

#2 (**permalink**) 02-28-2008, 10:27 AM

lister wrote:
> Hi all,
>
> I have a table where I need entries to be unique across two columns.
> ie:
>
> NAME UNIQUE
> john true
> john false
> john false
>
> I can have as many john false's as I like, but only ever one john
> true.
>
> I need to prevent two people inserting a john true at the same time.
> As far as I can see, the unique contraint can only be applied to a
> single column so I can't use the built in stuff.
>
> It appears to my newbie eyes that the only way I can do this is with a
> table write lock. ie:
> Lock the table for writes
> Do a SELECT to see if the unique entry is already in there.
> Insert it if it isn't
> Unlock the table.
>
> Are there any quicker / more efficient ways? Also I'm a bit unsure of
> the syntax to only insert if the select comes up empty. Can anyone
> point me in the right direction?
>
> Many thanks,
> Lister
>

You can create unique constraints for multiple columns, but they won't
help you here - you said "john false" could be duplicated.

Your lock/unlock will work fine. Probably the easiest way to do it.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

#3 (**permalink**) 02-28-2008, 10:27 AM

"lister" <listerofsmeg01@hotmail.com> wrote in message
news:1189712056.128679.217400@22g2000hsm.googlegro ups.com...
> Hi all,
>
> I have a table where I need entries to be unique across two columns.
> ie:
>
> NAME UNIQUE
> john true
> john false
> john false
>
> I can have as many john false's as I like, but only ever one john
> true.
>
> I need to prevent two people inserting a john true at the same time.
> As far as I can see, the unique contraint can only be applied to a
> single column so I can't use the built in stuff.
>
> It appears to my newbie eyes that the only way I can do this is with a
> table write lock. ie:
> Lock the table for writes
> Do a SELECT to see if the unique entry is already in there.
> Insert it if it isn't
> Unlock the table.
>
> Are there any quicker / more efficient ways? Also I'm a bit unsure of
> the syntax to only insert if the select comes up empty. Can anyone
> point me in the right direction?
>
> Many thanks,
> Lister
>

What version of MySQL are you using?

Evan

#4 (**permalink**) 02-28-2008, 10:27 AM

On Sep 13, 10:51 pm, "Evan Keel" <evank...@sbcglobal.net> wrote:

> What version of MySQL are you using?

5.0 at the moment, but there is no reason why I can't upgrade.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Unique constraint across columns?