3

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).

przno
  • 3,476
  • 4
  • 31
  • 45

1 Answers1

2

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
Giorgi Nakeuri
  • 35,155
  • 8
  • 47
  • 75
  • Thanks, can you please also explain what the `WITH CHECK` is doing (I guess you can not reference non-existent IDs ?) and why the 3rd statement (`... CHECK CONSTRAINT ...`) is needed? – przno Jun 29 '15 at 07:31
  • You can skip the last check. It is generated by `ssms`. You can read about this for example here http://stackoverflow.com/questions/529941/with-check-add-constraint-followed-by-check-constraint-vs-add-constraint – Giorgi Nakeuri Jun 29 '15 at 07:35
  • Found the answers [here](http://dba.stackexchange.com/questions/24297/alter-table-check-constraint). In my particular case I will use `WITH NOCHECK`. – przno Jun 29 '15 at 07:39