Given the next example:
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- Backup, backup & backup your database.
1- Due you are trying to convert varchar to varbinary and there isn't an explicit conversion you should add new columns to your tables. NOTE: You cannot make it non 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- Copy current values to the new columns:
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- Drop current constraints:
ALTER TABLE B DROP CONSTRAINT [FK_B];
ALTER TABLE C DROP CONSTRAINT [FK_C];
ALTER TABLE A DROP CONSTRAINT [PK_A];
4- Once you have checked new values are correct, drop actual columns:
ALTER TABLE A DROP COLUMN [ID];
ALTER TABLE B DROP COLUMN [A_ID];
ALTER TABLE C DROP COLUMN [A_ID];
5- Rename new columns with old names:
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- Make new columns not nullables:
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- Add constraints again:
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- Check final result:
SELECT * FROM A;
SELECT * FROM B;
SELECT * FROM C;
| ID |
| :------- |
| 0x303031 |
| 0x303130 |
| 0x313030 |
ID | A_ID
-: | :-------
1 | 0x303031
2 | 0x303031
3 | 0x303130
ID | A_ID
-: | :-------
4 | 0x303130
5 | 0x313030
6 | 0x313030
db<>fiddle here