CREATE [UNIQUE {ON FIRST {COLUMN | n_unique_column COLUMNS}} INDEX ON table_name (column_name1, column_name2 ...);
I would use the first (simple) syntax and just throw an error if the user tries to skip a column on the UNIQUE clause.
Seems, second option looks as more natural extension of CREATE UNIQUE INDEX
True, but it's awefully verbose. :( And...
It surprised me that you can INCLUDE extra columns on non-UNIQUE indexes, since you could just add them as regular indexed columns for the same effect. It looks like when you do that in SQL Server, the extra columns are only stored on btree leaf pages and so can't be used for searching or ordering. I don't know how useful that is or if we would ever want it... but I just wanted to note that difference, and that the proposed UNIQUE ON FIRST n COLUMNS syntax and catalog change can't express that.
... we might want to support INCLUDE at some point. It enhances covering scans without bloating the heck out of the btree. (I'm not sure if it would help other index types...) So it seems like a bad idea to preclude that.
I don't see that UNIQUE ON FIRST precludes also supporting INCLUDE. Presumably we could do either
CREATE INDEX ... ON table (f1, f2, f3) UNIQUE(f1, f2) INCLUDE(f4); or CREATE UNIQUE ON FIRST 2 COLUMNS INDEX ... ON table (f1, f2, f3) INCLUDE(f4);
Personally, I find the first form easier to read.
+1
I guess the standard CREATE UNIQUE INDEX can be seen as shorthand for CREATE INDEX with all columns listed in the UNIQUE clause.
Are we certain that no index type could ever support an index on (f1, f2, f3) UNIQUE(f1, f3)? Even if it doesn't make sense for btree, perhaps some other index could handle it.
That's certainly an interesting question. At the moment, only btree is capable of enforcing uniqueness, but that's not to say it will always be that way. But I guess you'd need a way for the access method list of defining whether it's capable of multi-column indexes with out-of-order unique columns. (or some more sensible way of describing it)
