Stumbled on this while doing some local refactoring... Dan, I think with the UNIQUE INDEX the table constraint is not necessary. We have something like this locally:
BEGIN;
ALTER TABLE metabib.browse_entry
DROP CONSTRAINT browse_entry_sort_value_value_key;
DROP INDEX metabib.browse_entry_sort_value_idx;
CREATE INDEX browse_entry_sort_value_idx
ON metabib.browse_entry (SUBSTRING(sort_value FROM 1 FOR 2700));
CREATE UNIQUE INDEX browse_entry_sort_value_value_key
ON metabib.browse_entry
USING btree (MD5(value), MD5(sort_value));
COMMIT;
Duplicate inserts on metabib.browse_entry will result in unique constraint violations.
If this looks sane, I can push a working branch.
Stumbled on this while doing some local refactoring... Dan, I think with the UNIQUE INDEX the table constraint is not necessary. We have something like this locally:
BEGIN;
ALTER TABLE metabib. browse_ entry entry_sort_ value_value_ key; browse_ entry_sort_ value_idx;
DROP CONSTRAINT browse_
DROP INDEX metabib.
CREATE INDEX browse_ entry_sort_ value_idx browse_ entry (SUBSTRING( sort_value FROM 1 FOR 2700));
ON metabib.
CREATE UNIQUE INDEX browse_ entry_sort_ value_value_ key browse_ entry
ON metabib.
USING btree (MD5(value), MD5(sort_value));
COMMIT;
Duplicate inserts on metabib. browse_ entry will result in unique constraint violations.
If this looks sane, I can push a working branch.