將外鍵類型從 char 遷移到 binary - 處理後果的方法？

我正在精簡一直使用char/varchar欄位的數據庫，其中binary/varbinary將是更好的選擇（假設所表示的數據是字節數組）。

我正在更改的欄位之一用於許多其他表的外鍵約束。

我不能刪除目前的 FK 約束，將列遷移到它的新類型，然後簡單地重新創建 FK 約束，因為數據類型不匹配。

我應該採取什麼方法來遷移它？你會怎麼做？

給定下一個範例：

CREATE TABLE A 
(
   [ID] VARCHAR(10) NOT NULL,
   CONSTRAINT [PK_A] PRIMARY KEY ([ID])
);

CREATE TABLE B 
(
   [ID] INT PRIMARY KEY, 
   [A_ID] VARCHAR(10) NOT NULL,
   CONSTRAINT [FK_B] FOREIGN KEY ([A_ID]) REFERENCES A([ID])
);

CREATE TABLE C 
(
   [ID] INT PRIMARY KEY, 
   [A_ID] VARCHAR(10) NOT NULL,
   CONSTRAINT [FK_C] FOREIGN KEY ([A_ID]) REFERENCES A([ID])
);

INSERT INTO A VALUES ('001'), ('010'), ('100');
INSERT INTO B VALUES (1, '001'), (2, '001'), (3, '010');
INSERT INTO C VALUES (4, '010'), (5, '100'), (6, '100');

0-備份，備份和備份您的數據庫。

1- 由於您正在嘗試將 varchar 轉換為 varbinary 並且沒有顯式轉換，您應該向表中添加新列。注意：您不能將其設為非 nullables。

ALTER TABLE A ADD [ID_VB] VARBINARY(10);
ALTER TABLE B ADD [A_ID_VB] VARBINARY(10);
ALTER TABLE C ADD [A_ID_VB] VARBINARY(10);

2-將目前值複製到新列：

BEGIN TRANSACTION
 UPDATE A SET [ID_VB] = CAST([ID] AS VARBINARY(10));
 UPDATE B SET [A_ID_VB] = CAST([A_ID] AS VARBINARY(10));
 UPDATE C SET [A_ID_VB] = CAST([A_ID] AS VARBINARY(10));
COMMIT TRANSACTION

3-刪除目前約束：

ALTER TABLE B DROP CONSTRAINT [FK_B];
ALTER TABLE C DROP CONSTRAINT [FK_C];
ALTER TABLE A DROP CONSTRAINT [PK_A];

4-檢查新值是否正確後，刪除實際列：

ALTER TABLE A DROP COLUMN [ID];
ALTER TABLE B DROP COLUMN [A_ID];
ALTER TABLE C DROP COLUMN [A_ID];

5-用舊名稱重命名新列：

EXEC sp_rename 'A.ID_VB', 'ID', 'COLUMN';
EXEC sp_rename 'B.A_ID_VB', 'A_ID', 'COLUMN';
EXEC sp_rename 'C.A_ID_VB', 'A_ID', 'COLUMN';

6-使新列不可為空：

ALTER TABLE A ALTER COLUMN [ID] VARBINARY(10) NOT NULL;
ALTER TABLE B ALTER COLUMN [A_ID] VARBINARY(10) NOT NULL;
ALTER TABLE C ALTER COLUMN [A_ID] VARBINARY(10) NOT NULL;

7-再次添加約束：

ALTER TABLE A ADD CONSTRAINT [PK_A] PRIMARY KEY ([ID]);
ALTER TABLE B ADD CONSTRAINT [FK_B] FOREIGN KEY ([A_ID]) REFERENCES A([ID]);
ALTER TABLE C ADD CONSTRAINT [FK_C] FOREIGN KEY ([A_ID]) REFERENCES A([ID]);

8-檢查最終結果：

SELECT * FROM A;
SELECT * FROM B;
SELECT * FROM C;

| 身份證 |
| :------- |
| 0x303031 |
| 0x303130 |
| 0x313030 |

身份證 | 援助 
-: | :-------
 1 | 0x303031
 2 | 0x303031
 3 | 0x303130

身份證 | 援助 
-: | :-------
 4 | 0x303130
 5 | 0x313030
 6 | 0x313030

db<>在這裡擺弄

為了保持關係，兩個數據需要具有相同的數據類型。

我正在更改的欄位之一用於許多其他表的外鍵約束。

所以我看不到另一種解決方案：

在表 A 中創建一個新列，並將表 B 中的約束更改為這個新列：

1 - Create another column (colX) in table A
2 - Cast and repply date into column colX 
3 - Remove constraint FK of table B
4 - Cast data type in table B
5 - Create a new constraint FK in table B with colX of table A

您可以創建一個觸發器來填充表 A 中的這個新列，但如果您使用觸發器，請務必小心。在未來的行動中是必要的文件和謹慎。

引用自：https://dba.stackexchange.com/questions/251575