I have a large, frequently accessed table that needs a primary key constraint added. The table already has a unique index (but not a unique constraint) on one of its columns. The overly simplified schema looks like this:
CREATE TABLE table_without_pk (not_a_pk integer not null, some_data text);
CREATE UNIQUE INDEX not_a_pk_idx ON table_without_pk (not_a_pk);
It looks like 9.1 added syntax that would allow us to create the primary key constraint off of the existing unique index, but unfortunately we're on 8.4. A post from several years ago (http://archives.postgresql.org/pgsql-general/2004-12/msg01161.php) implies that one can update the metadata tables underneath in order to accomplish this:
UPDATE pg_index i SET indisprimary = 't' FROM pg_stat_user_indexes u WHERE u.indexrelid = i.indexrelid AND u.indexrelname = 'not_a_pk_idx';
The underlying purpose is to get Londiste to acknowledge the table's key, and this strategy seems to work without any problems. Londiste doesn't seem to care that the "primary key" is only reflected in pg_index and isn't accompanied by the relevant pg_constraint entry. Is modifying the underlying pg_catalog tables like this "Very Bad"? Will it have mysterious and unintended consequences, or can I get away with it? Thanks!
Cheers,
Dave
