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).


Do you find this FAQ incorrect or incomplete? Please e-mail us what needs to be changed. To ensure quality, each change is checked by our editors (and often tested on live Firebird databases), before it enters the main FAQ database. If you desire so, the changes will be credited to your name. To learn more, visit our add content page.

If you are a commercial tool maker and your tool features a great way to handle the issue written about in this FAQ, please check out our advertisement page.



All contents are copyright © 2007-2017 FirebirdFAQ.org unless otherwise stated in the text.


Links   Firebird   News   FlameRobin   Home Inventory powered by FB  
Add content   Advertise   About  

Categories
 Newbies
 SQL
 Installation and setup
 Backup and restore
 Performance
 Security
 Connectivity and API
 HOWTOs
 Errors and error codes
 Miscellaneous