Mailing List Archive


Back to the month index Back to the list index
David J. Hughes (bambi@Hughes.com.au)
Mon, 12 Apr 1999 11:14:07 +1000 (EST) 

Date: Mon, 12 Apr 1999 11:14:07 +1000 (EST)
From: "David J. Hughes" <bambi@Hughes.com.au>
Subject: Re: un-enforced "unique" indices
Message-ID: <Pine.BSF.3.96.990412111149.1348D-100000@fawn.hughes.com.au>

On Sat, 10 Apr 1999, James E. Harrell wrote:

> Having a problem with unique indices... The index will enforce UNIQUE
> when you attempt to insert *new* data, but when you update current data
> in an indexed field, it allows non-unique data to be created... any
> suggestions?

Can you provide a test script that produces this as our tests show that an
UPDATE is subjected to unique index testing

        mSQL > update foo set v1 = '1' where v1 = '123456789'
            -> \g

        ERROR : Non unique value for unique index

> The create index clause looks like this:
> CREATE UNIQUE INDEX idx_cid on categories (categoryID)
> CREATE UNIQUE INDEX idx_cid_gid on categories (category_name, groupID)
>
> Shouldn't this allow duplicate categories ONLY if the groupID is
> different? In other words, several different groups can have categories
> by the same name. But a single group can not have 2 categories by the
> same name.

No. If the combination of 'categor_name,groupID' is different then it's a
unique index value.

Bambi
...