What are the challenges you face when working across database platforms? Take the survey
Options

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

MattOGMattOG Posts: 8 New member
edited October 3, 2019 2:02PM in SQL Change Automation
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
Tagged:

Answers

Sign In or Register to comment.