How can we help you today? How can we help you today?

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

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
MattOG
0

Comments

5 comments

Sort by
  • Tianjiao_Li
    Thanks for pointing out this issue.

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

    Thanks.
    Tianjiao_Li
    0
  • 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.
    Tianjiao_Li
    0
  • MattOG
    Thanks
    MattOG
    0
  • Tianjiao_Li
    I'm pleased to let you know the fix has been shipped. Please upgrade to the latest version.
    Tianjiao_Li
    0
  • MattOG
    Thank you. We found another situation that had similar symptoms so will check them out and report back.

    MattOG
    0

Add comment

Please sign in to leave a comment.