Defining composite and unique indexes

[CodesInChaos]

How do you define a composite index in EdgeDB?

The documentation states:

The simplest form of index is an index, which references one or more properties directly:

type User {
    property name -> str;
    index on (__subject__.name);
}

but I couldn't find a way to reference multiple properties in an index.

---

How do you define a unique index in EdgeDB?

For simple properties you can add constraint exclusive which implicitly creates a unique index.

required property name -> str {
    constraint exclusive;
}

However you can't add constraint exclusive to an explicitly defined index. How do you mark such an index as unique?

[elprans]

but I couldn't find a way to reference multiple properties in an index.

Use a tuple, like this:

type User {
    property first_name -> str;
    property last_name -> str;
    index on ((.first_name, .last_name));
}

You can use any (scalar) expression inside the parentheses, actually.

However you can't add constraint exclusive to an explicitly defined index. How do you mark such an index as unique?

Recent nighlies have the ability to define constraints on types:

type User {
    property first_name -> str;
    property last_name -> str;
    constraint exclusive on ((.first_name, .last_name));
}

[elprans]

I opened #1609 so we don't forget to document this properly.

[CodesInChaos]

I assume that a tuple index behaves like one would expect from an ordered index when querying the underlying properties?

These uses the index?

SELECT User FILTER User.first_name = "John";
SELECT User FILTER User.first_name = "John" && User.last_name = "Doe";

While this does not?

SELECT User FILTER User.last_name = "Doe"

[elprans]

That's correct.

[CodesInChaos]

And constraint on also acts as an ordered unique index?

Is it possible to define partial (unique) indexes? Something like constraint exclusive on (.name) where (not .deleted);

[elprans]

And constraint on also acts as an ordered unique index?

Yes. exclusive constraint is implemented in terms of an implicit unique index.

Is it possible to define partial (unique) indexes?

Not yet. That should be a straightforward improvement, though. I filed #1610 to track it.