Reputation: 3504
How do I change an existing column from bit
to a foreign key (int
)?
For example, column NEW_B
was created like this:
ALTER TABLE [dbo].[APPLICATION]
ADD [NEW_B] [bit] NULL
GO
But now I want the NEW_B
to reference column ID
(int) of table ATTACHMENT
(want to keep the name NEW_B
, also allow NULLs
).
Upvotes: 3
Views: 9828
Reputation: 35780
Here is the syntax:
--alter existing column to int
ALTER TABLE [dbo].[APPLICATION] ALTER COLUMN [NEW_B] INT NULL
GO
--add foreign key constraint
ALTER TABLE [dbo].[APPLICATION] WITH CHECK ADD CONSTRAINT [FK_APPLICATION_ATTACHMENT] FOREIGN KEY([NEW_B])
REFERENCES [dbo].[ATTACHMENT] ([ID])
GO
ALTER TABLE [dbo].[APPLICATION] CHECK CONSTRAINT [FK_APPLICATION_ATTACHMENT]
GO
Upvotes: 2