przno
przno

Reputation: 3504

Change column to foreign key

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

Answers (1)

Giorgi Nakeuri
Giorgi Nakeuri

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

Related Questions