Making a column NON NULL attempts to re-add a Foreign Key

MattOG

As per the title, have a table with a nullable column that has a foreign key and index already set. Change the column to be non nullable, then import and generate, the script is as below:

PRINT N'Dropping index [IX_index] from [schema].[table1]'

GO

DROP INDEX [IX_index] ON [schema].[table1]

GO

PRINT N'Altering [schema].[table1]'

GO

ALTER TABLE [schema].[table1] ALTER COLUMN [id_foreign_key] [uniqueidentifier] NOT NULL

GO

PRINT N'Creating index [IX_index] on [schema].[table1]'

GO

CREATE NONCLUSTERED INDEX [IX_index] ON [schema].[table1] ([IX_index])

GO

PRINT N'Adding foreign keys to [schema].[table1]'
GO

ALTER TABLE [schema].[table1] ADD CONSTRAINT [FK_schema.table1_schema.table2_id_foreign_key] FOREIGN KEY ([id_foreign_key]) REFERENCES [schema].[table2] ([id_foreign_key])

GO

The index has to be dropped and recreated as it references the column preventing the change, but at no point do I touch the foreign key.

Thanks

Tianjiao_Li

Thanks for pointing out this issue.

I'll speak to the development team and update you shortly.

Thanks.

Tianjiao_Li

The development team has identified the cause of the issue and logged it as SC-10672. I'll keep you posted with any progress.

Our apologies for the inconvenience caused.

Thanks.

MattOG

Thanks

Tianjiao_Li

I'm pleased to let you know the fix has been shipped. Please upgrade to the latest version.

MattOG

Thank you. We found another situation that had similar symptoms so will check them out and report back.