> -----Original Message----- > From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general- > owner@xxxxxxxxxxxxxx] On Behalf Of Mike Christensen > Sent: Wednesday, June 08, 2011 2:57 PM > To: pgsql-general@xxxxxxxxxxxxxx > Subject: Converting uuid primary key column to serial int > for each ingredient are kinda a hassle to manage though, and I'm thinking in > this case I might just want to use a 32bit integer for the primary key.. > 2) Once I do this, I obviously need to fix up all the foreign keys and convert > them from the old UUIDs to the new generated numeric value. > Is there an easy way to manage this sort of thing? I'm thinking about keeping > the old UUID as a non-PK column to use as a "mapping" as I migrate the rest > of the schema over. Is that a good approach? > What specific hassles are you encountering? Seems like a lot of effort to go through to make the data less-unique. Since you already have the field setup for PK/FK use try to leave that alone and just add a secondary identifier that you use for queries. Like you said in #2 leave the UUID in place as a non-PK (but still unique/not-null) field and leave all the foreign keys in place as well. Add your "integer identity" column as the new Primary Key and use that field when you want to specify a record. Then, for those cases where you have or want to use the integer PK but the field being compared is the UUID you can write a simple function to return the UUID associated with the given integer. Otherwise you probably should just add the integer field to EVERY TABLE and establish relational links. Add the field as null-able, perform the update using the UUID, change to not null, add FK constraint. As you then attempt to remove the UUID field from the tables the system will tell you where different function and views are relying upon the UUID field and you can change the object to use the new integer field instead. The main risk really is in the application since the database will not be able to enforce consistency. Serial is implemented via "DEFAULT" and sequences; and if you dump/restore a sequence it is done consistently. David J. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
