Modify autoincrement column to global autoincrement column

Our company uses Sybase and we are planning on setting up a Mobilink system (data replication system). We therefore need to change from using autoincrement columns to global autoincrememnt columns.

My question is what steps do开发者_开发问答 I need to take to get this working properly. There is already thousands of rows of data that used the regular autoincrement default.

I'm thinking I need to create a new column with a default of global autoincrement, fill it with data (number(*)), switch the PK to it, drop the old FK's, drop the old column, rename the new column to the old one, then re-apply the FK's.

Is there an easier way to accomplish what I need here?

thanks!

---

That's generally the way to go about it. But there are some specific statements you make that cause me concern. Also the sequence. I am not sure of your experience level, the terms you use may or may not be accurate.

For each table ...

... switch the PK to it

What about the FK values in the child tables ? Or do you mean you will change them as well ?

... drop the old FK's

Ok, that's the constraint.

... drop the old column, rename the new column to the old one, then re-apply the FK's.

What exactly do you mean by that ? Add the FK constraint back in ? That won't change the existing data, it will apply to any new rows added.

Hope you see what I mean by the sequence of your tasks is suspect. Before you drop the old_PK_column in the parent, you need to:

1. Add the dropped FK constraints in each child table.

2. For each child table: UPDATE all the FK values to the new_PK_column.

3. Then drop the old_PK_column.

---

you're just changing the way PK values are generated, so it's enough to:

ALTER TABLE <table>
  modify <column> default global autoincrement (1000000); 

to use a partition size of 1,000,0000

Also make sure you set the global database identifier in each db, for example:

SET OPTION PUBLIC.global_database_id = 10;

So the next PK that will be generated is 10,000,001