FireBird替换已使用的Table的复合主键

PROFILE

数据表的主键应该独立于有实际意义的字段,因为实际业务中总会有奇葩的逻辑让你的复合主键(实际意义的数据列)出现重复,为空等等.
更改已存在数据的表的主键的步骤
1, 添加主键列
2, 更新已存在数据行的主键列
3, 删除已存在主键,添加主键到新增主键列
4, Note:

    删除主键报错: 索引正在使用 
        主键自带主键索引，数据库会缓存索引。简单粗暴的方法是直接重启firebird server

FIREBIRD 数据库

SET TERM #;
ALTER TABLE TEST ADD TESTGUID VARCHAR(36) NOT NULL#
COMMIT#
EXECUTE BLOCK 
AS
DECLARE VARIABLE V_TESTNAME VARCHAR(50); 
DECLARE VARIABLE V_TESTLEVEL SMALLINT; 
DECLARE VARIABLE V_GUID VARCHAR(36);  
BEGIN
   FOR SELECT TESTNAME,TESTLEVEL FROM TEST INTO :V_TESTNAME, :V_TESTLEVEL
   DO BEGIN 
        SELECT P.NEWFBID FROM UFNEWFBGUID P INTO :V_GUID;

        UPDATE TEST SET TESTGUID=:V_GUID WHERE TESTNAME =:V_TESTNAME AND TESTLEVEL = :V_TESTLEVEL;   
   END
END#
COMMIT#
SET TERM ;#
ALTER TABLE TEST DROP CONSTRAINT PK_TEST;
COMMIT;
ALTER TABLE TEST ADD CONSTRAINT PK_TEST_GUID PRIMARY KEY (TESTGUID);
COMMIT;

DATABASE DEFINATION LANGUAGE：

CREATE TABLE TEST
(
  TESTLEVEL SMALLINT NOT NULL,
  TESTNAME VARCHAR(50) NOT NULL,
  TESTPASSWORD VARCHAR(20),
  CONSTRAINT PK_TEST PRIMARY KEY (TESTLEVEL, TESTNAME)
); 
GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
 ON TEST TO  SYSDBA WITH GRANT OPTION; 

UFNEWFBGUID

SET TERM ^ ;
ALTER PROCEDURE UFNEWFBGUID
RETURNS (
    NEWFBID VARCHAR(32) )
AS
DECLARE VARIABLE I INTEGER;
DECLARE VARIABLE C INTEGER;
DECLARE VARIABLE REAL_UUID CHAR(16) CHARACTER SET OCTETS;
BEGIN
    REAL_UUID = GEN_UUID();
    NEWFBID = '';
    I = 0;
    WHILE (I < 16) DO
        BEGIN
            C = ASCII_VAL(SUBSTRING(REAL_UUID FROM I+1 FOR 1));
            IF (C < 0) THEN C = 256 + C;
            NEWFBID = NEWFBID
            || SUBSTRING('0123456789ABCDEF' FROM BIN_SHR(C, 4) + 1 FOR 1)
            || SUBSTRING('0123456789ABCDEF' FROM BIN_AND(C, 15) + 1 FOR 1);
            I = I + 1;
        END
SUSPEND;
END^
SET TERM ; ^