Sql-server – Migrate foreign key type from char to binary – ways to deal with the fallout

ddlforeign keymigrationsql server

I am in the process of slimming down a database that has been using char/varchar fields where binary/varbinary would be a better choice (given the data represented is byte arrays).

One of the fields I am changing is used in foreign key contraints on a number of other tables.

I cannot drop the current FK constraints, migrate the column to its new type and then simply re-create the FK constraints as the data types would not match.

What approach should I take in migrating this? How would you go about it?

Best Answer

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

Related Question