maria-developers team mailing list archive

[Sergei Golubchik <serg@xxxxxxxxxxx>]

Hi, Jared!

On Dec 27, Jared Beck wrote:
> On Wed, Dec 27, 2017 at 3:30 PM, Sergei Golubchik <serg@xxxxxxxxxxx> wrote:
> >> Any plans to implement partial indexes?
> >> (https://www.postgresql.org/docs/10/static/indexes-partial.html)
> >
> > No such plans at the moment.
> > What do you you need them for?
> 
> I have used unique partial indexes to enforce business rules like
> "Active users must have a unique phone number, but there may be any
> number of inactive users with the same phone number"
> 
> ```
> create unique index ix_active_phones
> on users (phone)
> where active = true
> ;

That's a good use case. It's basically a "partial constraint", not
partial index. In MariaDB (and in SQL standard) the way to achieve that
would be to use a nullable column for active, and mark inactive users
with NULL. Then you can create a unique index on (phone,active).

Regards,
Sergei
Chief Architect MariaDB
and security@xxxxxxxxxxx