0

I have a table:

MyTable{
      Id int not null;
      Name varchar not null;
      TypeId int null;
}

Now i want to make my TypeId column is not null.
First fill it with data:

update [dbo].[MyTable]
set [dbo].[MyTable].[TypeId] = 1
where [dbo].[MyTable].[TypeId] is null

And set not null:

go
ALTER TABLE [dbo].[MyTable] ALTER COLUMN [TypeId] BIT NOT NULL;

But get an error:

Msg 5074, Level 16, State 1, Line 1
The object 'FK_MyTable_ObjTypes' is dependent on column 'TypeId'.

Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN TypeId failed because one or more objects access this column.

What to do in this case? I cant use after table if column is FK?

I tried disable constreints:

GO
ALTER TABLE [dbo].[MyTable] NOCHECK CONSTRAINT [FK_MyTable_ObjTypes];
go
ALTER TABLE [dbo].[MyTable] ALTER COLUMN [TypeId] BIT NOT NULL;
GO
ALTER TABLE [dbo].[MyTable] CHECK CONSTRAINT [FK_MyTable_ObjTypes];

But get same error.

Drop and recreate constrains help me. This is my query:

GO
ALTER TABLE [dbo].[MyTable] DROP CONSTRAINT [FK_MyTable_ObjTypes];
go
ALTER TABLE [dbo].[MyTable] ALTER COLUMN [TypeId] INT NOT NULL;
GO
ALTER TABLE [dbo].[MyTable] WITH NOCHECK
ADD CONSTRAINT [FK_MyTable_ObjTypes] FOREIGN KEY ([TypeId]) REFERENCES [dbo].[ObjTypes] ([Id]);
GO
ALTER TABLE [dbo].[MyTable] WITH CHECK CHECK CONSTRAINT [FK_MyTable_ObjTypes];
Kliver Max
  • 5,107
  • 22
  • 95
  • 148
  • 1
    It looks like you have to drop FK_MyTable_ObjTypes first, then you have to alter column and then you have to recreate FK_MyTable_ObjTypes. I think, it's not because of NULL, it's due to you are changing column type from INT to BIT – Kartic Mar 31 '15 at 10:33

2 Answers2

4

Your Foreign Key FK_MyTable_ObjTypes references the TypeId column, so changing the column would invalidate the constraints of the FK.

You will need to remove the FK first (script it as a CREATE so you can see what the existing definition is). Then ALTER your column, before recreating the FK using the newly modified column.

Widor
  • 13,003
  • 7
  • 42
  • 64
3

Yo can disable the FK 'FK_MyTable_ObjTypes', alter the column and enable again the FK:

How can foreign key constraints be temporarily disabled using T-SQL?

Community
  • 1
  • 1
Jabuciervo
  • 86
  • 3