The current Unique constraint relies directly upon a Unique index to test for uniqueness.
This has two disadvantages: * only B-Trees currently support Uniqueness * We need to create an index on *all* of the columns of the primary key, which may end up being a very large index as a result The uniqueness check code searches for the value being inserted and if a value is found that is visible, then we reject. We currently use the same index scan key for the uniqueness check as we do for the index search. If the uniqueness check used a scan key that consisted of all of the Primary Key columns, rather than just the index columns then it would be able to scan through non-unique index entries to check uniqueness. Interestingly, the current uniqueness check code already scans through multiple tuples because of the possible existence of multiple row versions. So we just need to supply a different scan key. If we extended the definition of a PRIMARY KEY to include an existing index like this ALTER TABLE foo ADD PRIMARY KEY (...) USING INDEX index_name; then we would be able to specify what we want. This would then allow us to use a Hash Index or other index as the basis for a Unique Constraint and/or considerably reduce size of indexes. Frequently the full unique key could be 5 or 6 columns, even though the leading columns might be sufficiently unique to make this technique worthwhile. It's also common to want to store the hash() of a value rather than the value itself, but the hash typically won't be guaranteeably unique, even though the probability of collisions may be very low. Thoughts? -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com PostgreSQL UK 2008 Conference: http://www.postgresql.org.uk -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers