Re: Question: unique on multiple columns

On Thu, Feb 06, 2003 at 03:25:53PM -0600, Bruno Wolff III wrote:
> On Thu, Feb 06, 2003 at 10:33:51 -0600,
> will trillich <will(at)serensoft(dot)com> wrote:
> >
> > of course, my example is rather bogus -- as others pointed out,
> > unique(a,c) means that there'll only be 1 b for any a/c pair, so
> > the unique(b...) index is not-too-useful. it's just an
> > illustration, and a bad one at that. ah, well...
>
> If there are several different foreign key references into the table
> using different combinations of a, b and c, you might need both indexes.

yes, particularly if you have

index ( a, b )
index ( a, c )
index ( b, c )

then any searching on field A or B will use an index; anything
using either with C will also use an index.

but in my fabricated example, something like

index ( a, b )
index ( b, a, c )

the second index doesn't add a whole lot more functionality than
just plain old

index ( b )

would have.

--
There are 10 kinds of people:
ones that get binary, and ones that don't.

will(at)serensoft(dot)com
http://sourceforge.net/projects/newbiedoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!

Looking for a firewall? Do you think smoothwall sucks? You're
probably right... Try the folks at http://clarkconnect.org/ !