How to move primary key to another column

Sometimes you need change the primary key column(s) of a table. Here are the required steps:

Make sure database table is not used by other users while doing this.

1. You must eliminate all dependencies involving the old PK (foreign keys, triggers, etc.). Constraints and views need to be dropped, triggers and procedures can be altered to have empty bodies. Save the source code of all those objects in a file, as you will need it to recreate those afterwards (with alterations, as required).

Using FlameRobin can help you a lot in this task: open the Properties page for the table and click the # symbol next to the primary key column in the column list. FlameRobin will track all the dependencies and build the two-part script. First part of this script drops all the dependencies, taking care of the order if there are internal dependencies between those as well. This first part ends with SQL comment DROPPING COMPLETE. You need to execute that first part now, and save the second part for later. Second part recreates back all the dependencies.

If you have multiple PK columns, click the # symbol for each of those and execute all the DROP parts sequentially (i.e. click #, generate, run, click second #, generate, run, click third #, generate, run...). Make sure you store all the regeneration scripts as well, and execute them later in reverse order (i.e. what was dropped last, should be re-created first).


2. Find out the name of the primary key, let's suppose your table is MYTABLE and the name of the PK is PK_MYTABLE. You can do this by looking at Constraints in FlameRobin or doing SHOW TABLE in ISQL.


3. Add the new column:

ALTER TABLE MYTABLE ADD ID BIGINT NOT NULL;
COMMIT;


3.1. (optional) In case you wish to change from natural to surrogate keys, you would need to populate the new column with unique values. Generators are usually used for this purpose. The following statements create a new generator, create a trigger that will keep adding new values for newly inserted records and finally populates the existing rows:

CREATE GENERATOR GEN_PK_MYTABLE;

CREATE TRIGGER POP_ID FOR MYTABLE ACTIVE BEFORE INSERT
AS BEGIN
IF (NEW.ID IS NULL) THEN NEW.ID = GEN_ID(GEN_PK_MYTABLE, 1);
END
COMMIT;

UPDATE MYTABLE SET ID = GEN_ID(GEN_PK_MYTABLE, 1);


4. (optional) If you like, you can move the new column to the first position in the column list:

ALTER TABLE MYTABLE ALTER ID POSITION 1;


5. Create the new primary key:

ALTER TABLE MYTABLE
ADD CONSTRAINT PK_MYTABLE PRIMARY KEY(ID);


6. Now, go back and recreate the dropped triggers, foreign keys, etc. using the script generated by FlameRobin in step 1. Do not run the script blindfolded, examine it before. You might need to change the column name in some places, and maybe do other corrections (for example, if new primary key column has a different datatype).