3

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?

5

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

  • I think this is a good approach, but if you are going to go to these lengths, I would think changing the the RI to use a surrogate key instead of the raw data would be a better approach in general, though that may require some additional code changes on the app side. – John Eisbrener Oct 21 at 14:37
  • You can make it none nullable by adding a default (e.g. 0x). Adding a column with a (runtime constant) default is a metadata only change in Enterprise Edition. Dropping the default when needed later is a metadata only change unlike changing the column property to not null - which is a size of data operation – Martin Smith Oct 22 at 7:14
  • @MartinSmith thanks. – McNets Oct 22 at 7:57
2

To keep the relationate both data need has same data type.

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

So I can't see another solution to do this:

Create a new column in table A and change constraint in table B to this new column:

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

You can create a trigger to populate this new column in table A, but keep caution if you use trigger. Is necessary documentation and caution in future actions.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.