Re: WIP: generalized index constraints

Jeff Davis wrote:
> I'm going to try to get this patch ready for the 9-15 commitfest. Here
> are a few design updates:
>
> (1) Language:
>
> I think that the new language should be a table constraint, and I think
> there's a consensus on that. The specific language I have in mind is:
>
> CREATE TABLE (
> ...,
> INDEX CONSTRAINT (<attname> <op>, ...) USING INDEX <indexname>
> );

That sounds like the constraint is based on an existing index, but there
can't be any existing indexes on a table that hasn't been created yet.
If this creates the index, then the syntax needs to support specifying
index access method and an opclass for all the columns.

> ALTER TABLE ADD INDEX CONSTRAINT (<attname> <op>, ...)
> USING INDEX <indexname>;
>
> Where <op> is the constraint operator. For example, if all <op>s are
> "=" (or whatever the operator for BTEqualStragey is for that type), that
> would be semantically identical to a UNIQUE constraint.

This makes more sense.

It would be nice to have syntax to create the index and constraint in
one command, so that the constraint can be checked while the index is
being created. Otherwise you need an extra heap scan to check it.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com