Change SQL primary key

change_primary_key.sql

CREATE TABLE tbl_A(col1 VARCHAR(20) NOT NULL PRIMARY KEY);

CREATE TABLE tbl_B(
    id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    col1 VARCHAR(20) NOT NULL,
    col2_id integer NOT NULL,
    col3_id integer NULL,
    FOREIGN KEY (col2_id) REFERENCES tbl_A(col1),
    FOREIGN KEY (col3_id) REFERENCES tbl_C(id)
);

CREATE TABLE tbl_C(id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT);



CREATE TABLE tbl_A_new(id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, name VARCHAR(20) NOT NULL);
INSERT INTO tbl_A_new(`name`) SELECT col1 from tbl_A order by `col1`;

CREATE TABLE tbl_B_new(
    id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    col1 VARCHAR(20) NOT NULL,
    col2_id integer NOT NULL,
    col3_id integer NULL,
    FOREIGN KEY (col2_id) REFERENCES tbl_A_new(id),
    FOREIGN KEY (col3_id) REFERENCES tbl_C(id)
);


INSERT INTO tbl_B_new(`id`, `col1`, `col2_id`, `col3_id`)
SELECT tbl_B.id, tbl_B.col1, tbl_A.id, tbl_B.col3_id FROM tbl_A_old as tbl_B
LEFT JOIN tbl_B_new as tbl_A ON tbl_B.id = tbl_A.col1;


DROP TABLE tbl_A;
ALTER TABLE tbl_A_new RENAME TO tbl_A;
DROP TABLE tbl_B;
ALTER TABLE tbl_B_new RENAME TO tbl_B;