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 ; ^