By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 648391 fiddles created (15115 in the last week).
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])
);
✓
hidden batch(es)
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');
9 rows affected
hidden batch(es)
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);
✓
hidden batch(es)
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
9 rows affected
hidden batch(es)
ALTER TABLE B DROP CONSTRAINT [FK_B];
ALTER TABLE C DROP CONSTRAINT [FK_C];
ALTER TABLE A DROP CONSTRAINT [PK_A];
✓
hidden batch(es)
ALTER TABLE A DROP COLUMN [ID];
ALTER TABLE B DROP COLUMN [A_ID];
ALTER TABLE C DROP COLUMN [A_ID];
Caution: Changing any part of an object name could break scripts and stored procedures.
Caution: Changing any part of an object name could break scripts and stored procedures.
Caution: Changing any part of an object name could break scripts and stored procedures.
hidden batch(es)
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;
✓
hidden batch(es)
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]);
✓
hidden batch(es)
SELECT * FROM A;
SELECT * FROM B;
SELECT * FROM C;