Rename primary key field to Id

From Gestinux Wiki
Jump to navigation Jump to search


Some tables were created in early versions 0.x with a primary key different of Id. There is an attempt to change this, in order to have an Id field primary key of each table. This is rather complex if we want to ensure upward and downward compatibility, with both DBMS MySql and PostgreSql.

This should be possible in 4 steps, that is 4 Gestinux versions.

In the version 1.4 we have performed the step 3 for the tables Moves and MoveTemplates.

In the version 1.5 we plan to do the same for the tables MoveLines and MoveTemplatesLines.


<source lang="sql"> -- clean up DROP TABLE IF EXISTS table2 ; DROP TABLE IF EXISTS table1 ;

-- ----------------------------------- STEP 1 (INITIAL) -------------------------------------------------------------------- CREATE TABLE table1 ( old_key INT(10) NOT NULL PRIMARY KEY AUTO_INCREMENT, donnee VARCHAR(50) NULL );

CREATE TABLE table2 ( old_key INT(10) NOT NULL, donnee VARCHAR(50) NULL,

  KEY table2_K1 (old_key),
  CONSTRAINT table2_C1 FOREIGN KEY (old_key) REFERENCES table1 (old_key)

ON DELETE CASCADE );

INSERT INTO table1 (donnee) VALUES ('test1'); INSERT INTO table2 (old_key,donnee) VALUES ( 1, 'test1'); -- forbidden : INSERT INTO table2 (old_key,donnee) VALUES ( 2, 'test2');

SELECT * FROM table1 JOIN table2 ON table2.old_key=table1.old_key;

-- ----------------------------------- STEP 2 --------------------------------------------------------------------

ALTER TABLE table1 ADD COLUMN Id INT NULL AFTER donnee;

CREATE INDEX table1_K1 ON table1 (Id); -- must be not unique for backward compatibility

-- During upgrade : duplicate old_key into Id UPDATE table1 SET Id=old_key WHERE Id IS NULL;

-- If upgrading from STEP 3, the old primary key must not be re-created, -- auto_inc property must remain on Id, and foreign keys must not be changed

-- In this step : -- We change a field auto_inc only when it is created, never if already existing (I.E coming from a next version) -- Create a primary key or a foreign key (specified in property) only when not already existing


SELECT * FROM table1 JOIN table2 ON table2.old_key=table1.Id;

-- After inserting a record, update the field which is not auto_inc, old_key when coming from STEP 3, or Id if STEP 3 was never used

-- ----------------------------------- STEP 3 -------------------------------------------------------------------- -- During upgrade, when coming from STEP 4, the datamodule change the primary key (deleting and re-creating all foreign keys) -- old_key is deleted from the table, and will thus not be created if coming from STEP 4

ALTER TABLE table2 DROP FOREIGN KEY table2_C1; ALTER TABLE table1 CHANGE COLUMN old_key old_key INT(10) NULL DEFAULT NULL; ALTER TABLE table1 DROP PRIMARY KEY; ALTER TABLE table1 DROP INDEX table1_K1; -- now useless ALTER TABLE table1 ADD PRIMARY KEY(Id); ALTER TABLE table1 CHANGE COLUMN Id Id INT(10) NOT NULL AUTO_INCREMENT; ALTER TABLE table2 ADD FOREIGN KEY table2_C1 (old_key) REFERENCES table1 (Id) ON DELETE CASCADE; -- must keep the same name

-- When inserting we update the old_key after a new record is inserted, to allow backward compatibility -- Every reference to the old key must be removed at this step

INSERT INTO table1 ( donnee) value ('test3'); SELECT * FROM table1 JOIN table2 ON table2.old_key=table1.Id;

-- ----------------------------------- STEP 4 (FINAL) -------------------------------------------------------------------

ALTER TABLE table1 DROP COLUMN old_key; SELECT * FROM table1 JOIN table2 ON table2.old_key=table1.Id; </source>