-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 I have a need to provide a way to change a table's primary key columns, in the possible presence of foreign keys, and with concurrent use (as much as possible).
The best way I have come up with is roughly: a. create the to-be-new-pk index concurrently b. "demote" the old pk to be a unique constraint c. alter table add constraint new pk using existing index Step b. involves (in one transaction): - -------------------------------------- * update pg_class row for the table relhaspkey false * update pg_constraint row for the original pk contype = 'u' conname = 'some_new_name' * update pg_index row for the original pk index indisprimary = false * alter index original pk rename to some_new_name - -------------------------------------- I have tested this (minimally) and as far as I can tell it works. Questions: 1) any major holes in this approach? 2) any better ideas? 3) should we consider an ALTER TABLE ALTER CONSTRAINT command (or some such) to allow demotion of a PRIMARY KEY to a UNIQUE constraint? Thanks for any thoughts/comments. Joe - -- Joe Conway credativ LLC: http://www.credativ.us Linux, PostgreSQL, and general Open Source Training, Service, Consulting, & 24x7 Support -----BEGIN PGP SIGNATURE----- Version: GnuPG v1 iQIcBAEBAgAGBQJUf0t5AAoJEDfy90M199hlUT8QAJcobqTmT0fKfBaXbIjXzJLZ ++i0JC/RwDOfnNkUjrEkv+WlQlhwHViKd04JCLWKes8EE81Vv8qUTlQDzphxeHCS OiPMKSePHzCSphYRwzGp1gurvfcw/Yv/5wQyCcUWBqpa4DYZzUFKkeoH3d2Zzd6z t6iR8cM21XArFnGMQN1gmAA0swStrm6CdzxydEIY3EoY8tgIGQBjDAEzs/v4bYPG kEECRYtNu3tiy+ejslB+WWPXd0y+Ty8idRpmgMPMLqIi0RzA8s/p4xhm1INUyr31 fqlN3vaFaPEgXdsuDEqmXt7H0QWyCIwEYelPTh3Zi8RmOOzzeZKNdjaQc6mJHrO7 JFVFMVaw/CRuogui9Q/DMalpbNWjbqoVV4JtPssGK1BBvmpJrEd7QLzFLmz3QSzn Rdb/UN8RWWAfL50MAztlSpwX/4vPbolvC7yMjg1lGvfm8g0B3qz+iHW3V0G1qX8Y mxQD3LvnMgUN/m2EUiUr+L+Eh3fEV0M3SbQCii6b+apLjVGe25pHE8zx4QufrJKk ftX70nRJPaoW/+LBj69n7r7wde3CSpI2/6qbjIXYKu/gutWQEPgxbbd4fxTGICz/ P16y2V4mwKT66Ma3vjz1gwCGcHSShLsJx4PAGFMpR3SE63kDvGE0Zm/RG5u9+z/r MIkw1EL3cFpTlu+7Jtwj =pyzW -----END PGP SIGNATURE----- -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
